Take:

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 is a JSON object referring to writers.id
- John supervises Andy, Hank, Alex, and Joe
- Bill supervises Ken, Zach, Walt, and Mike
writers.projects is a JSON object referring to projects.id
- Andy handles Boston and Chicago
- Hank handles Cisco, Seattle, and North
- et cetera

...John and Bill don't write; they supervise writers listed by writers.id in writers.supervising JSON.

The writers have papers they write...

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  |

I need to work with the supervisors and the papers:

Get a list of all projects.id by writers under John's supervision.
Check and see if:

John (writers.id=1) is supervising the "Carlisle" (projects.id=13) project (0 rows)
Bill (writers.id=2) is supervising the "Carlisle" (projects.id=13) project (1 row)

What I need:

I need something like...

Get a list of all projects.id by writers under John's supervision (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';

Desired result:

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

Check if John (id 1) supervises Carlisle (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';

Desired result: 0 rows

I don't think either is right. But, I know that I'm looking through permutations of two JSON objects.

CodePudding user response：

When I understand you problem correct, the result should be:

supervisor	writer	title
John	Hank	Cisco
John	Hank	Seattle
John	Hank	North
John	Alex	West
John	Alex	West

WITH RECURSIVE cte as (
   SELECT 0 as x
   UNION ALL
   SELECT x 1 FROM cte WHERE X<10),
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 project
from writers 
cross join cte
where JSON_VALUE(supervising,CONCAT('$[',cte.x,']')) is not null
)

select 
   s.name  as supervisor, 
   w.name  as writer,
   p.title
from tbl_supervisors s
left join tbl_writers w on w.project = s.project
inner join projects p on p.id = s.project
where s.name = 'John'

see: DBFIDDLE

With the cte's I am first creating two tables (tbl_writers) and (tbl_supervisors). SQL works better on tables than on JSON-formatted data, because there was no JSON when SQL was born.