SQL JOIN 두 개의 JSON 열, 두 개의 관련 ID별

SQL JOIN 두 개의 JSON 열, 두 개의 관련 ID별

여러 테이블을 참조하는 관련된 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는 다음을 가리키는 JSON 객체입니다.writers.id
  • 존은 앤디, 행크, 알렉스, 그리고 조를 감독합니다.
  • 빌은 켄, 잭, 월트, 마이크를 감독합니다.
• writers.projects는 다음을 가리키는 JSON 객체입니다.projects.id
  • 앤디는 보스턴과 시카고를 관리합니다.
  • 행크는 시스코, 시애틀, 북부 지역을 담당합니다.
  • 등

...존과 빌은 글을 쓰지 않습니다. 그들은 목록에 있는 작가들을 감독합니다.writers.id에writers.supervisingJSON.

그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존의 감독하에 작가들에 의해.
2. 다음 사항을 확인합니다.

• 존(writers.id=1) "Carlisle"을 감독하고 있습니다(projects.id=13) 프로젝트(0열)
• 청구서(writers.id=2) "Carlisle"을 감독하고 있습니다.projects.id=13) 프로젝트(1열)

필요한 것:

그런 게 필요해요

1. 모두 목록 가져오기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    |

2. John(존) 여부)idCarlisle 감독(id13)

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 개체의 순열을 살펴보고 있다는 것을 알고 있습니다.

문제가 해결된 경우 다음과 같은 결과가 나올 것입니다.

감독관	작가.	프로젝트.	p_id
존.	앤디	보스턴	1
존.	앤디	시카고	2
존.	행크	시스코	3
존.	알렉스야.	마이애미	7
존.	행크	북쪽	5
존.	행크	시애틀	4
존.	조.	서울	10
존.	조.	타이난	9
존.	알렉스야.	서쪽	6
존.	알렉스야.	요크	8

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

참조: db<>fiddle

공통 테이블 표현식(CTE)을 사용하여 먼저 두 개의 테이블을 만듭니다. (tbl_writers) 및 (tbl_supervisorsSQL은 JSON 형식의 데이터보다 테이블에서 더 잘 작동합니다. SQL이 생성되었을 때 JSON이 없었기 때문입니다.

언급URL : https://stackoverflow.com/questions/72515789/sql-join-two-json-columns-by-two-related-ids