2

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.

4

The error message is telling you that you can't use a set returning in the SELECT list. You need to put it into the FROM clause:

SELECT array_agg(t.k) 
FROM table tbl, jsonb_object_keys(tbl.data) as t(k)
GROUP BY tbl.some_id 
WHERE tbl.some_id = 3;
  • thanks for the answer, it works now - I wanted to edit your typos in line 2 (t -> tbl, a -> as) but it's less than 6 characters so SO won't let me – narrowtux Feb 16 '18 at 12:54

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.