Postgres query to return JSON object keys as array

Question

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"}'

Answer 1

Sure, with json_object_keys(). This 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;

Or use jsonb_object_keys() for jsonb.

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)) AS keys) k ON true;