postgres : How to achieve json_agg like functionality on json_object_keys

Question

If I want to get the list of json object's keys aggregated in a json array in a single query. Here is what I am trying and it gives me an error:

Postgres version : 9.3

postgres=# create temporary table t_test ( 
postgres(# id integer,
postgres(# options json
postgres(# );
CREATE TABLE
postgres=# insert into t_test values (1, '{"x": 1, "y": 2}');
INSERT 0 1
postgres=# select * from t_test ;
 id |     options      
----+------------------
  1 | {"x": 1, "y": 2}
(1 row)

postgres=# select json_object_keys(options) from t_test ;
 json_object_keys 
------------------
 x
 y
(2 rows)

postgres=# select json_agg(json_object_keys(options)) from t_test ;
ERROR:  set-valued function called in context that cannot accept a set

Clodoaldo Neto · Accepted Answer · 2014-09-11 14:52:05Z

1

select json_agg(o)
from (
    select json_object_keys(options) as o
    from t_test
) s
;
  json_agg  
------------
 ["x", "y"]

answered Sep 11, 2014 at 14:52

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres : How to achieve json_agg like functionality on json_object_keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related