JSON array를 join 하는 법

json array 안의 id값을 join 하는 방법

AClass table 안의 한 column의 구조가 json array라고 가정하자

CREATE TABLE AClass (id INT, studentInfo JSON);
insert into AClass VALUES (1, JSON_ARRAY(json_object('id', 1, 'name', 'Mike', 'mathClassId', 1), json_object('id', 2, 'name', 'Jake', 'mathClassId', 2)));

select StudentInfo from AClass
  	=> [{id: 1, name: 'Mike', mathClassId: 1}, 
    	{id: 1, name: 'Jake', mathClassId: 2}]

그리고 mathClass table이 있다고 가정

CREATE TABLE mathClass (id INT, className text, classRoomNo INT, teacher text);
insert into mathClass VALUES(1, 'beginner', 3, 'Sam');
insert into mathClass VALUES(2, 'expert', 9, 'Jane');

select * from mathClass 
 => id | className | classRoomNo | teacher |
 --------------------------------------------
 	1  | beginner  | 3           | Sam     |
    2  | expert    | 9   	     | Jane	   |

일때 mathClass의 id와 AClass의 StudentInfo안의 mathClassId를 연결해 원하는 정보(class의 name을 알고싶다)를 나열해보자

SELECT JSON_ARRAYAGG(JSON_OBJECT("className", math.className)) FROM AClass as class
    JOIN JSON_TABLE(class.studentInfo, '$[*].mathClassId' COLUMNS (category INT PATH '$')) AS cats
    JOIN mathClass math ON cats.category = math.id
    GROUP BY class.id;
    
    => [{"className": "beginner", "mathclassId": 1}, {"className": "expert", "mathclassId": 2}]