在JSON格式的数组中联接多个联接表并分组结果

vwoqyblh 于 2023-08-08 发布在其他

关注(0)|答案(2)|浏览(92)

假设我有一桌人：

+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Mary |
|  3 | Jane |
+----+------+

字符串
以及用于各种类型的服装的各种table，例如一table鞋子

+----+----------+--------------------+---------+
| id |  brand   |        name        |  type   |
+----+----------+--------------------+---------+
|  1 | Converse | High tops          | sneaker |
|  2 | Clarks   | Tilden cap Oxfords | dress   |
|  3 | Nike     | Air Zoom           | running |
+----+----------+--------------------+---------+

型
然后对于每种服装类型，我有一个连接表，记录每个人拥有的那种类型的物品：

+--------+---------+
| person |  shirt  |
+--------+---------+
|      1 |       3 |
|      1 |       4 |
|      2 |       2 |
...

型
我需要一个查询，将这些表编译成一个返回值，如下所示：

+----+------+--------------------+
| id | name |   clothing items   |
+----+------+--------------------+
|  1 | John | [JSON in a string] |
|  2 | Mary | [JSON in a string] |
|  3 | Jane | [JSON in a string] |
+----+------+--------------------+

型
其中每行的[JSON in a string]应该如下所示：

[
   {"type":"shirt","id":3},
   {"type":"shirt","id":4},
   {"type":"pant","id":2},
   {"type":"shoe","id":5}
]

型
如何在SQLITE中构造这个查询？如果需要，我可以很容易地定义自己的自定义聚合函数。

JSON

来源：https://stackoverflow.com/questions/76847779/joining-multiple-junction-tables-and-grouping-results-in-a-json-formatted-array

2条答案

按热度按时间

vfwfrxfs1#

想明白了

WITH cte AS (SELECT person, ('[' || GROUP_CONCAT(clothing,',') || ']') AS clothing
  FROM (SELECT person, json_object('type','shirt','id',shirts) AS clothing
    FROM junction_shirts
    UNION
    SELECT person, json_object('type','pant','id',pants) AS clothing
    FROM junction_pants
    UNION
    SELECT person, json_object('type','shoe','id',shoes) AS clothing
    FROM junction_shoes)
GROUP BY person)
SELECT p.id, p.name, c.clothing
FROM people p LEFT JOIN cte c
ON c.person = p.id;

字符串
有关演示，请参见dbfiddle

赞(0）回复(0）举报 2023-08-08

jrcvhitl2#

您可以通过使用此查询获得结果。首先，我测试了第一个json对象，然后将它们从每个用户ID和名称数组中分组到一个数组中：

select 
t1.id
    ,t1."name"
    ,json_agg(new_object)
    from
(
select 
    se.id
    ,se."name"
    ,json_build_object('type',sa.type,'id',sa.id) as  new_object
from junction_table as  ss
inner join  shoe_table as sa on ss.shirt = sa.id
inner join people_table as se on ss.person = se.id 
)t1
group by t1.id,t1."name";

字符串

赞(0）回复(0）举报 2023-08-08

我来回答

在JSON格式的数组中联接多个联接表并分组结果

2条答案

相关问题

热门标签

最新问答