How can I get all keys from a JSON column in Postgres?

user_1667207 0
Viewed 58735

If I have a table with a column named json_stuff, and I have two rows with

{ "things": "stuff" } and { "more_things": "more_stuff" }

in their json_stuff column, what query can I make across the table to receive [ things, more_things ] as a result?

postgresql json
5 Answers

Here is the example if you want to get the key list of each object:

select array_agg(json_keys),id from (
select json_object_keys(json_stuff) as json_keys,id from table) a group by a.id

Here id is the identifier or unique value of each row. If the row cannot be distinguished by identifier, maybe it's better to try PL/pgSQL.

user_60481860

Insert json_column and table

select distinct(tableProps.props) from (
select jsonb_object_keys(<json_column>) as props from <table>
) as tableProps
user_14386030

I wanted to get the amount of keys from a JSONB structure, so I'm doing something like this:

select into cur some_jsonb from mytable where foo = 'bar';
select into keys array_length(array_agg(k), 1) from jsonb_object_keys(cur) as k;

I feel it is a little bit wrong, but it works. It's unfortunate that we can't get an array directly from the json_object_keys() function. That would save us some code.

user_37635730
Related