我有一个存储如下值的表:
| id | thing | values |
|----|-------|--------|
| 1 | a |[1, 2] |
| 2 | b |[2, 3] |
| 3 | a |[2, 3] |
并希望使用聚合函数按thing分组，但只存储数组的唯一值，这样结果将是:
| thing | values |
|-------|---------|
| a |[1, 2, 3]|
| b |[2, 3] |
有没有一种简单而有效的方法可以在研究生中做到这一点？
首先，将JSON数组与json_array_elements()分开——这是一个集合返回函数，带有JOIN LATERAL，得到一行id、thing，每个元素有一个JSON数组元素.
然后为thing和value Select DISTINCT条记录，按value排序.
最后你把记录加起来，加上json_agg().
在SQL中，它看起来像:
SELECT thing, json_agg(value) AS values
FROM (
SELECT DISTINCT thing, value
FROM t
JOIN LATERAL json_array_elements(t.values) AS v(value) ON true
ORDER BY value) x
GROUP BY thing
一般来说，你会希望使用jsonb型，因为它比json型更有效.然后你必须使用相应的jsonb_...()个函数.