Re: [GENERAL] json select question
This is probably the start of your quest into this project. No doubt you’ll need to handle other queries. So my suggestion is first to rationalise the data storage before digging any deeper. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of CS DBA Sent: 22 September 2016 00:54 To: pgsql-general Subject: [GENERAL] 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
Re: [GENERAL] json select question
On 9/21/2016 4:54 PM, CS DBA wrote: 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
[GENERAL] 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