如何避免SQLite中json_group_array生成重复内容？

编写SQLite查询时遇到分类重复问题，简化场景涉及四张表：Project、Facility_category、Facility_item、Project_Facility_Relation。执行查询后，Category字段中每个Facility_category会根据Project_Facility_Relation中的关联条目数重复出现，例如"Relax Outdoor"重复3次，"School"重复2次。

表结构及数据 #

-- Project表
id | name
-----------
1  | Building A
2  | Apartment B

-- Facility_category表
id | name
---------------------
1  | Relax Outdoor
2  | School

-- Facility_item表
id | name
---------------------
1  | Square
2  | Kid Zone
3  | Swimming pool
4  | High School A
5  | University B

-- Project_Facility_Relation表
id | project_id | category_id | item_id
----------------
1  | 1          | 1           | 1
2  | 1          | 1           | 2
3  | 1          | 1           | 3
4  | 1          | 2           | 4
5  | 1          | 2           | 5

原查询语句 #

SELECT pr.id, pr.name,
  (
  SELECT DISTINCT json_group_array(
    json_object('id', qu.id, 
    'name', qu.name, 
    'item',  
        (
            SELECT DISTINCT json_group_array(json_object('id',  ifi.id, 'name', ifi.name))
            FROM Project_Facility_Relation fa
            INNER JOIN Facility_item ifi ON ifi.id = fa.item_id
            where fa.project_id = pr.id AND fa.category_id = qu.id
        )
    ))
  FROM Project_Facility_Relation fa
  INNER JOIN Facility_category qu ON qu.id = fa.category_id
  where fa.project_id = pr.id
  ) as Category
FROM Project pr
GROUP BY pr.id

（注：原语句中fa.qualily_id应为fa.category_id，fa.facility_id应为fa.item_id，属于笔误，已修正）

重复原因分析 #

外层子查询直接遍历Project_Facility_Relation的每一条关联记录，每一条记录都会对应生成一个分类的JSON对象。比如"Relax Outdoor"关联了3条记录，就会生成3个完全相同的分类对象；"School"关联2条记录，就生成2个相同对象。SQLite中DISTINCT无法对JSON对象去重（不同行的相同JSON对象会被视为不同值），最终导致分类重复出现在数组中。

修正后的查询语句 #

核心思路是先按分类分组，再为每个分组生成包含对应物品的JSON对象，最后合并成数组：

SELECT pr.id, pr.name,
  (
    SELECT json_group_array(
      json_object(
        'id', qu.id,
        'name', qu.name,
        'item', (
          SELECT json_group_array(json_object('id', ifi.id, 'name', ifi.name))
          FROM Project_Facility_Relation fa
          JOIN Facility_item ifi ON ifi.id = fa.item_id
          WHERE fa.project_id = pr.id AND fa.category_id = qu.id
        )
      )
    )
    FROM Facility_category qu
    WHERE EXISTS (
      SELECT 1 FROM Project_Facility_Relation fa
      WHERE fa.project_id = pr.id AND fa.category_id = qu.id
    )
    GROUP BY qu.id
  ) AS Category
FROM Project pr
GROUP BY pr.id

执行效果 #

修正后Category字段中每个分类只会出现一次，对应包含该分类下的所有关联物品：

[
  {
    "id": "1",
    "name": "Relax Outdoor",
    "item": [
      {"id": "1", "name": "Square"},
      {"id": "2", "name": "Kid Zone"},
      {"id": "3", "name": "Swimming pool"}
    ]
  },
  {
    "id": "2",
    "name": "School",
    "item": [
      {"id": "4", "name": "High School A"},
      {"id": "5", "name": "University B"}
    ]
  }
]

内容的提问来源于stack exchange，提问作者codeforfun