I have a table that holds JSON data. In a query with a GROUP BY
clause, I'd like to get an array of all of the JSON field names in the result set.
I tried a query like this:
SELECT array_agg(jsonb_object_keys(data))
FROM table
GROUP BY some_id
WHERE some_id = 3
For an input data like
some_id | data
--------|---------------
3 | {"foo": "bar"}
4 | {"baz": 3}
3 | {"bar": 4}
I'd like to receive:
array_agg
--------------
{'foo', 'bar'}
But it returns an error: ERROR: set-valued function called in context that cannot accept a set
It seems like I need to somehow convert setof text
, which is what jsonb_object_keys
returns, into array
but I don't know how.
Best Answer
The error message is telling you that you can't use a set returning in the
SELECT
list. You need to put it into theFROM
clause: