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

• blong: Here's an additional postgresql JSON question you might find interesting: dba.stackexchange.com/q/281480/45101

Answer 1

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;