Thread: json select question

All;

I'm working with a client running postgres 9.2, they have a table with a "json_data_string" column of type json

the data looks something like this with lots of rows for each (i.e. lots of json_data_string->book_name rows, lots of json_data_string->catalog_name rows, etc:

'{ "book_name": "Book the Fourth", "author": { "first_name": "Charles", "last_name": "Davis" } }'
'{ "catalog_name": "Catalog the Fourth", "author": { "first_name": "Charles", "last_name": "Davis" } }'
'{ "magazine_name": "mag4", "author": { "first_name": "Charles", "last_name": "Davis" } }'
'{ "category_name": "region", "author": { "first_name": "Charles", "last_name": "Davis" } }'


How can i pull a unique list of all json column names? such as book_name, catalog_name, etc

Thanks in advance

How can i pull a unique list of all json column names? such as book_name, catalog_name, etc

try json_object_keys() ... https://www.postgresql.org/docs/current/static/functions-json.html

but this will only pull the top level keys, like (from the first row of your example) , book_name, author... it won't pull out any second level fields like first_name, last_name.

you'll likely need to run this on all rows, and do a group by to get what you need.

thats a pretty messy way of storing data, btw, and not very relational, having a key name indicate a record type, and tossing all the record types into the same table.

-- 
john r pierce, recycling bits in santa cruz