Is it possible to return a JSON object keys as an array of values in PostgreSQL?
In JavaScript, this would simply be Object.keys(obj), which returns an array of strings.
For example, if I have a table like this:
tbl_items
---------
id bigserial NOT NULL
obj json NOT NULL
And if there's a row like this:
id obj
----- -------------------------
123 '{"foo":1,"bar":2}'
How can I have a query to return:
id keys
----- ------------------
123 '{"foo","bar"}'
json_object_keys() (or jsonb_object_keys() for jsonb) returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it:
SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys
FROM tbl_items;
This returns an array of keys per row (not for the whole table).
A more verbose form would be to spell out a LATERAL join instead of the correlated subquery:
SELECT t.id, k.keys
FROM tbl_items t
LEFT JOIN LATERAL (
SELECT ARRAY(
SELECT * FROM json_object_keys(t.obj)
)
) k(keys) ON true;
