How can I get all 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 their json_stuff column, what query can I make across the table to receive [ 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're using just json.)

The PostgreSQL json documentation is quite good. Take a look.

And as it is stated in the documentation, the function only gets the outer most keys. So 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;