我有两个相关的JSON列引用多个表。
我需要匹配每一个结果排列。

服用：

writers：

| id | name | supervising (JSON)  | projects (JSON)   |
|:-- |:-----| :-------------------| :-----------------|
| 1  | John | ["3","4","5","6"]   | null              |
| 2  | Bill | ["7","8","9","10"]  | null              |
| 3  | Andy | null                | ["1","2"]         |
| 4  | Hank | null                | ["3","4","5"]     |
| 5  | Alex | null                | ["6","7","8"]     |
| 6  | Joe  | null                | ["9","10"]        |
| 7  | Ken  | null                | ["11","12","13"]  |
| 8  | Zach | null                | ["14","15","16"]  |
| 9  | Walt | null                | ["17","18"]       |
| 10 | Mike | null                | ["19","20","21"]  |

writers.supervising是引用writers.id的JSON对象
约翰监督安迪、汉克、亚历克斯和乔
比尔监督肯、扎克、沃尔特和迈克
writers.projects是引用projects.id的JSON对象
安迪负责波士顿和芝加哥
汉克负责思科、西雅图和北方
等等

......约翰和比尔不写信;他们监督writers.supervising JSON中writers.id列出的编写者。
writers有他们写的papers...

projects：

| id | title    |
|:-- |:---------|
| 1  | Boston   |
| 2  | Chicago  |
| 3  | Cisco    |
| 4  | Seattle  |
| 5  | North    |
| 6  | West     |
| 7  | Miami    |
| 8  | York     |
| 9  | Tainan   |
| 10 | Seoul    |
| 11 | South    |
| 12 | Tokyo    |
| 13 | Carlisle |
| 14 | Fugging  |
| 15 | Turkey   |
| 16 | Paris    |
| 17 | Midguard |
| 18 | Fugging  |
| 19 | Madrid   |
| 20 | Salvador |
| 21 | Everett  |

我需要和主管和报社合作
1.在约翰的监督下，获得所有作者的projects.id列表。
1.检查并查看：

约翰（writers.id =1）正在监督“卡莱尔”（projects.id =13）项目（0行）
Bill（writers.id =2）正在监督“Carlisle”（projects.id =13）项目（1行）

我需要什么：

我需要像...
1.获得John指导下的所有作者的projects.id列表（writers.id =1）。

SELECT p.id, p.title FROM projects p
JOIN writers w
WHERE JSON_CONTAINS(writer s ON s.supervising
  JSON_CONTAINS(w.projects)
)
AND s.id = '1';

预期结果：

| 1  | Boston   |
| 2  | Chicago  |
| 3  | Cisco    |
| 4  | Seattle  |
| 5  | North    |
| 6  | West     |
| 7  | Miami    |
| 8  | York     |
| 9  | Tainan   |
| 10 | Seoul    |

1.检查约翰（id 1）是否监督卡莱尔（id 13）

SELECT id FROM projects p
WHERE writer s JSON_CONTAINS(writer w ON s.supervising
  JSON_CONTAINS("13" ON p.id)
)
AND s.id = '1';

预期结果：0 rows个
我不认为这两种方法都是正确的，但是，我知道我正在查看两个JSON对象的排列。

WITH RECURSIVE cte AS ( SELECT 0 AS x UNION ALL SELECT x+1 FROM cte ), tbl_writers AS ( SELECT id, name, -- cte.x, JSON_VALUE(projects,CONCAT('$[',cte.x,']')) AS project FROM writers CROSS JOIN cte WHERE JSON_VALUE(projects,CONCAT('$[',cte.x,']')) IS NOT NULL ), tbl_supervisors AS ( SELECT id, name, -- cte.x, JSON_VALUE(supervising,CONCAT('$[',cte.x,']')) AS writer FROM writers CROSS JOIN cte WHERE JSON_VALUE(supervising,CONCAT('$[',cte.x,']')) IS NOT NULL ) -- SELECT * FROM tbl_supervisors; -- SELECT * FROM tbl_writers; SELECT s.name AS supervisor, w.name AS writer, p.title AS project, p.id AS p_id FROM tbl_supervisors s LEFT JOIN tbl_writers w ON w.id = s.writer INNER JOIN projects p ON p.id = w.project -- WHERE s.name = 'Bill' -- For Bill by name -- WHERE s.id = '2' -- For Bill by id -- WHERE s.name = 'John' -- For John by name WHERE s.id = '1' -- For John by id ORDER BY project; -- By project alphabetical -- ORDER BY p_id; -- Example ORDER BY option from out AS col

1条答案

按热度按时间

2ekbmq321#

当我把你的问题理解正确时，结果应该是：
| 监督员|记录器|投射|管道标识|
| - -|- -|- -|- -|
| 若翰|安迪|波斯顿|一个|
| 若翰|安迪|芝加哥|2个|
| 若翰|汉克|思科公司|三个|
| 若翰|亚历克斯|迈阿密市|七个|
| 若翰|汉克|北部地区|五个|
| 若翰|汉克|西雅图|四个|
| 若翰|乔|汉城|10个|
| 若翰|乔|台南市|九个|
| 若翰|亚历克斯|西部地区|六个|
| 若翰|亚历克斯|约克郡|八个|

请参阅：db<>fiddle
对于公共表表达式（CTE），我首先创建了两个表，（tbl_writers）和（tbl_supervisors）。SQL在表上比在JSON格式的数据上工作得更好，因为在SQL诞生时还没有JSON。

赞(0）回复(0）举报 2022-11-08

mariadb SQL JOIN两个JSON列，通过两个相关ID

服用：

我需要什么：

1条答案

相关问题

热门标签

最新问答