6

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.