postgresql 将select语句直接转换为json数组

pzfprimi 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(207)

从here和here中，我发现如果我想将一组相关的行聚合成一个对象数组，我必须使用以下语法：

(select to_json(C) from ( /* subquery */ ) C)

如果我有三张table：user、creature及其连接表user_creature：

我想检索每个用户，以及属于这个用户的每个生物，我必须这样做：

select to_json(T)
from (
         select "user".id        as user_id,
                (select to_json(C) -- !!! There it is
                 from (
                          select name, height
                          from creature
                                   inner join "user_creature" uc on creature.id = "uc".creature_id
                                   inner join "user" u on "uc".user_id = u.id
                          where u.id = user_id
                      ) C)       as "creatures" -- !!! There it is
         from "user"
     ) T;

此查询成功检索用户及其相关生物的列表：

有没有一种方法可以从查询中删除select和from关键字，这样我就可以像这样写我的查询：

select to_json(T)
from (
         select "user".id        as user_id,
                to_json( -- !!! Calling to_json directly on select statement
                      select name, height
                      from creature
                               inner join "user_creature" uc on creature.id = "uc".creature_id
                               inner join "user" u on "uc".user_id = u.id
                      where u.id = user_id
                  )  as "creatures"
         from "user"
     ) T;

postgresql

来源：https://stackoverflow.com/questions/68148510/converting-select-statement-directly-into-json-array

2条答案

按热度按时间

jum4pzuy1#

可以使用子查询作为to_json的参数，但不实用：

您需要将子查询 Package 在分组括号中：to_json( (SELECT … FROM …) )
子查询必须恰好返回一行（但这很正常）
子查询必须只返回一列。这有点困难-你可以返回一个记录，但是如果你动态地构建它（例如，从选择的列中，您几乎无法控制字段名称）

(See a demo here）。
如果只想写一个SELECT查询，则使用json_build_object：

SELECT json_build_object(
  'user_id', u.id,
  'creatures', (
    SELECT json_build_object(
      'name', c.name,
      'height', c.height
    )
    FROM creature c
    INNER JOIN "user_creature" uc ON c.id = uc.creature_id
    WHERE uc.user_id = u.id
  )
)
FROM "user" u;

如果你想检索多行，可以使用SELECT json_agg(json_build_object(…)) FROM …或ARRAY(SELECT json_build_object(…) FROM …)：

SELECT json_build_object(
  'user_id', u.id,
  'creatures', (
    SELECT json_agg(json_build_object(
      'name', c.name,
      'height', c.height
    ))
    FROM creature c
    INNER JOIN "user_creature" uc ON c.id = uc.creature_id
    WHERE uc.user_id = u.id
  )
)
FROM "user" u;

赞(0）回复(0）举报 2023-06-22

qacovj5a2#

对我来说是有效的

WITH sq AS
(
    -- YOUR QUERY HERE ⬇
    SELECT * 
    FROM "foo" 
    WHERE "id"='bar'
    -- YOUR QUERY HERE ⬆
)
SELECT json_agg(row_to_json(sq)) FROM sq

row_to_json-将所有列值打包为一个（按行）
json_agg-将所有行打包到一个单元格中

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 将select语句直接转换为json数组

2条答案

相关问题

热门标签

最新问答