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?
How can I get all keys from a JSON column in PostgreSQL?
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.
select jsonb_object_keys(json_stuff) as key from table group by key;
DISTINCT is significant slower than group by. Don't know why.
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;
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.
Here's a solution that implements the same semantics as MySQL's JSON_KEYS(), which...:
NULL safe (i.e. when the array is empty, it produces [], not NULL, or an empty result set)SELECT
o,
(
SELECT coalesce(json_agg(j), json_build_array())
FROM json_object_keys(o) AS j (j)
)
FROM (
VALUES ('{}'::json), ('{"a":1}'::json), ('{"a":1,"b":2}'::json)
) AS t (o)
Replace json by jsonb if needed.
Producing:
|o |coalesce |
|-------------|----------|
|{} |[] |
|{"a":1} |["a"] |
|{"a":1,"b":2}|["a", "b"]|
Insert json_column and table
select distinct(tableProps.props) from (
select jsonb_object_keys(<json_column>) as props from <table>
) as tableProps
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.
