将结果组合到JSON对象中

Question

我想知道是否可以以JSON对象的形式返回结果列表。我的查询比下面的要复杂得多，但是为了简单起见，可以表达我的目标。

SELECT 
    r.name, r.address
FROM "Locations" AS r 
LEFT JOIN "Units" AS f 
    ON r.city = f.city

每个位置都有许多潜在的单位，但这些单元将以JSON的形式返回，而不是修改我的完整表结构。

因此，我要查找的响应是每个位置的一行，列为: name (string)、address (string)和units (代表所有行的JSON)。

这在MySQL中是不可能的，因为我想知道我是否可以在Postgres中做到这一点。JSONd的数据并不大，所以我不太关心性能。

Answer 1

你想要的东西是：

SELECT 
    json_build_object(
      'name', r.name,
      'address', r.address,
      'units', json_agg( json_build_object(
        'number', f.number,
        -- etc etc fields of units here
      ))
    )
FROM "Locations" AS r 
LEFT JOIN "Units" AS f 
    ON r.city = f.city
GROUP BY r.id;

很难完全确定，因为您没有提供模式、示例数据、预期输出等等。

注意，您不能嵌套json_agg调用。如果有几个级别的连接，那么需要一种不同的方法来实现多层次的结果。尝试提供一些能更好地反映实际用例的内容，并提供示例数据和预期的结果(如果需要的话)。

您可能需要使用FROM中的一系列嵌套查询来重新构造嵌套级别，以获得所需的嵌套级别聚合。我认为条件向上和向下推仍然会阻止为要丢弃的行创建JSON，但还没有确切地验证这一点。