json - GROUP BY Postgresql中json类型列的内容

Question

我有一个 Postgresql 表，其中有一个名为“food”的 json 列。

下面是一些行的例子:

food
["cheese", "salmon", "eggs"]
["salmon", "cheese", "eggs"]
["broccoli", "ham", "milk"]
["salmon", "cheese", "eggs", "pizza"]

当前结果:

food                                       count
["cheese", "salmon", "eggs"]              | 1
["salmon", "cheese", "eggs"]              | 1
["broccoli", "ham", "milk"]               | 1
["salmon", "cheese", "eggs", "pizza"]     | 1

期望的结果:

food                                       count
["cheese", "salmon", "eggs"]              | 2
["broccoli", "ham", "milk"]               | 1
["salmon", "cheese", "eggs", "pizza"]     | 1

有没有办法在不考虑元素顺序的情况下对 json 字段的内容进行 GROUP BY？如果两行的内容相同，那么我希望将它们组合在一起。

我的计划是 GROUP BY json_array_elements(food)，但出于某种原因，这只返回每行的第一个元素。

Answer 1

其实和@Scoots 的回答很相似，但是没有排序，windows，aso:

SELECT (
    SELECT jsonb_agg(items order by items)
    FROM jsonb_array_elements(food) AS items
    ) AS food,
    count(*)
FROM test_json_grouping
GROUP BY 1;

...解释:

                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1635.60..1890.60 rows=200 width=40)
   Group Key: (SubPlan 1)
   ->  Seq Scan on test_json_grouping  (cost=0.00..1629.25 rows=1270 width=32)
         SubPlan 1
           ->  Aggregate  (cost=1.25..1.26 rows=1 width=32)
                 ->  Function Scan on jsonb_array_elements items  (cost=0.00..1.00 rows=100 width=32)
(6 rows)

结果:

                 food                  | count 
---------------------------------------+-------
 ["cheese", "eggs", "salmon"]          |     2
 ["broccoli", "ham", "milk"]           |     1
 ["cheese", "eggs", "pizza", "salmon"] |     1
(3 rows)