postgres：如何在json

postgres：如何在json_object_keys上实现类似json_agg的功能(postgres : How to achieve json_agg like functionality on json_object_keys)

如果我想在单个查询中获取聚合在json数组中的json对象的键列表。 这是我正在尝试的，它给了我一个错误：

Postgres版本：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

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

最满意答案

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