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

Question

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 the json_stuff column, what kind of query can I do in the table to get [ things, more_things ] as a result?

Answer 1

Use this:

 select jsonb_object_keys(json_stuff) from table; 

(Or just json_object_keys if you only use json.)

PostgreSQL json documentation is not bad. Take a look .

And as indicated in the documentation, the function receives only foreign keys. Therefore, if the data is a nested json structure, the function will not return any of the deeper keys.

Answer 2

 WITH t(json_stuff) AS ( VALUES ('{"things": "stuff"}'::JSON), ('{"more_things": "more_stuff"}'::JSON) ) SELECT array_agg(stuff.key) result FROM t, json_each(t.json_stuff) stuff; 

Answer 3

Here is an example if you want to get a list of keys for 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 string cannot be distinguished by identifier, it might be better to try PL/pgSQL .