Postgres: How to execute json_agg as functionality on json_object_keys

If I want to get a list of json object keys aggregated in json array in one request. 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

      

+3


source to share


1 answer


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

      



+1


source







All Articles