postgresql - 如何在 postgresQL 中的 json 值的 array_agg 中组合 DISTINCT 和 ORDER BY

Question

注意:我使用的是最新版本的 Postgres (9.4)

我正在尝试编写一个查询，它对 2 个表进行简单连接，并按第一个表的主键进行分组，并对第二个表中的几个字段进行 array_agg，我希望将其作为对象返回。该数组需要通过json对象中2个字段的组合进行排序，并且也是唯一化的。

到目前为止，我已经想出了以下内容:

SELECT  
  zoo.id,  
  ARRAY_AGG(
    DISTINCT ROW_TO_JSON(( 
      SELECT x  
      FROM ( 
        SELECT animals.type, animals.name 
      ) x
    ))::JSONB
    -- ORDER BY animals.type, animals.name
  )
  FROM zoo
  JOIN animals ON animals.zooId = zoo.id
  GROUP BY zoo.id;

这会为每个动物园生成一行，其中包含一组 jsonb 对象，每个动物一个，唯一。

但是，我似乎无法弄清楚如何通过代码注释部分中的参数对其进行排序。

如果我去掉不同的字段，我可以按原始字段进行排序，效果很好，但是我有重复项。

Answer 1

如果您使用 row_to_json()，您将丢失列名，除非您输入的是已键入的行。如果您使用显式名称使用​​ json_build_object() “手动”构建 jsonb 对象，那么您会取回它们:

SELECT zoo.id, array_agg(za.jb) AS animals
FROM zoo
JOIN (
  SELECT DISTINCT ON (zooId, "type", "name")
    zooId, json_build_object('animal_type', "type", 'animal_name', "name")::jsonb AS jb
  FROM animals
  ORDER BY zooId, jb->>'animal_type', jb->>'animal_name'
  -- ORDER BY zooId, "type", "name" is far more efficient
) AS za ON za.zooId = zoo.id
GROUP BY zoo.id;

您可以ORDER BY jsonb 对象的元素，如上所示，但是(据我所知)您不能使用DISTINCT 在 jsonb 对象上。在您的情况下，这无论如何都是相当低效的(首先构建所有 jsonb 对象，然后丢弃重复项)并且在聚合级别上使用标准 SQL 显然是不可能的。但是，您可以通过在构建 jsonb 对象之前应用 DISTINCT 子句来获得相同的结果。

另外，避免使用 SQL key words像“类型”和标准数据类型，如“名称”作为列名。两者都是非保留关键字，因此您可以在适当的上下文中使用它们，但实际上您的命令可能会变得非常困惑。例如，您可以有一个模式、一个表、该表中的一列和一个数据类型，每个都称为“类型”，然后您可以得到这个:

SELECT type::type FROM type.type WHERE type = something;

虽然 PostgreSQL 会欣然接受这一点，但它充其量只是令人困惑，并且在各种更复杂的情况下容易出错。您可以通过双引号任何关键字来获得很大的帮助，但最好避免将它们作为标识符。