Get nested keys hstore postgres

587 Views Asked by At

I'm trying to get the nested key's of the following 2 strings, saved as hstore in an postgresdb:

{"values"=>"{\"60\"=>[\"hallo\"], \"63\"=>[\"9\"], \"62\"=>[\"Nein\", \"Vielleicht\"], \"61\"=>[\"Ja\"]}", "comment"=>"[\"hmm\"]"}"},
{"values"=>"{\"60\"=>[\"test?\"], \"63\"=>[\"9\"], \"62\"=>[\"Ja\", \"Nein\"], \"61\"=>[\"Ja\"]}", "comment"=>"[\"kommentar23\"]"}"}

to be clear, how can I get all the keys/numbers in sql?

I tried the akeys function and get "values", thats fine, thats the first key, but I don't know how to get the nested keys? I tried with subqueries with no success.

any advice?

Thanks, Patrick

1

There are 1 best solutions below

0
On

I'd say your hstore looks more like twisted JSON. You can try to replace => with : and parse it, for example, with python json module or, if you're using 9.3, with native json methods.

Or you can get skeys, convert it to hstore again and get values.