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

Solution 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.

Solution 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;

Solution 3:

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.