postgreSQL查询jsonb列中的空数组字段

device_id | device
-----------------------------
9809      | { "name" : "printer", "tags" : [] }    
9810      | { "name" : "phone", "tags" : [{"count": 2, "price" : 77}, {"count": 3, "price" : 37} ] }

对于包含数组“tags”的 jsonb 列“device”上的以下 postgres SQL 查询：

SELECT t.device_id, elem->>'count', elem->>'price'
FROM   tbl t, json_array_elements(t.device->'tags') elem
where t.device_id = 9809

device_id 是主键。

我有两个问题不知道如何解决：

1. Tags 是一个可能为空的数组字段，在这种情况下我得到 0 行。无论标签是否为空，我都想要输出。虚拟值没问题。
2. 如果标签包含多个元素，我会得到同一设备 ID 的多行。如何将这些多个元素聚合到一行中？

您的第一个问题可以通过使用左外连接来解决，它将用 NULL 值替换右侧缺失的匹配项。

第二个问题可以用聚合函数来解决，例如json_agg, array_agg or string_agg，取决于所需的结果类型：

SELECT t.device_id,
       jsonb_agg(elem->>'count'),
       jsonb_agg(elem->>'price')
FROM tbl t
   LEFT JOIN LATERAL jsonb_array_elements(t.device->'tags') elem
      ON TRUE
GROUP BY t.device_id;

你将得到一个 JSON 数组，其中仅包含null对于数组为空的那些行，我希望这对您来说没问题。