表"ORDER":
ID | ORDERN_NUM |
---|---|
1 | 123 |
2 | 321 |
3 | 456 |
表"RECEIVERS":
FIRST_NAME | ID | LAST_NAME |
---|---|---|
Pete | 1 | Tyler |
Sarah | 1 | Bowden |
Bart | 2 | Simpson |
表"PRODUCTS":
TYPE | ID |
---|---|
Towel | 1 |
Pen | 1 |
Spoon | 2 |
我们可以说"FIRST_NAME"和"TYPE"是各自表的主键,但这对这个问题并不重要.重要的是"ORDER"的主键是"ID",而"ID"是"PRODUCTS"和"RECEIVERS"中的外键.
我试图开发一个单一的查询,允许用户在"RECEIVERS.FIRST_NAME"上查询.然后,查询将连接所有三个表,并返回与"ID"=1相关的所有内容.因此,如果我要查询"Pete",将返回如下所示的对象:
[{
"ID": "1",
"ORDERN_NUM": "123",
"RECEIVERS": [{
"FIRST_NAME": "Pete",
"LAST_NAME": "Tyler"
},
{
"FIRST_NAME": "Sarah",
"LAST_NAME": "Bowden"
}],
"PRODUCTS": [{
"TYPE": "Towel"
},
{
"TYPE": "Pen"
}]
}]
到目前为止,我有这样的疑问:
SELECT COUNT(*),
JSON_ARRAYAGG(JSON_OBJECT('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
FROM ORDER AS o
INNER JOIN (SELECT ID,
JSON_ARRAYAGG(JSON_OBJECT('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
FROM RECEIVERS
GROUP BY ID) r on o.ID = r.ID
INNER JOIN (SELECT ID,
JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
FROM PRODUCTS
GROUP BY ID) p on o.ID = p.ID
WHERE RECEIVERS.FIRST_NAME = "Pete"
然而,当搜索FIRST_NAME = 'Pete'
时,它确实会在嵌套数组中带回与Pete关联的所有"ORDER"S和所有"PRODUCTS",并在嵌套数组中带回Pete的详细信息,但它不会带回Sarah作为"RECEIVER".
我怎样才能做到这一点呢?