tbl_partsというテーブルにdoc_idsというdoc_idのJSON配列のカラムがあり、tbl_docsではそのdoc_idで各種ドキュメント情報を管理しているとして、このIDをoriginalカラムに置き換えてみる。
タグなどの管理にも応用できるかもしれない。
> SELECT part_id, doc_ids FROM tbl_parts
+---------+---------+
| part_id | doc_ids |
+---------+---------+
| 1 | [1, 2] |
| 2 | NULL |
| 3 | NULL |
| 4 | [3] |
| 5 | NULL |
| 6 | NULL |
+---------+---------+
ドキュメント管理テーブル側
> SELECT doc_id, original FROM tbl_docs ORDER BY doc_id;
+--------+-----------+
| doc_id | original |
+--------+-----------+
| 1 | xxxxx.pdf |
| 2 | abc.xlsx |
| 3 | zzz.pdf |
+--------+-----------+
JSONのキーで結合することができる
https://blog.comilab.net/post/2018-04-10/
https://stackoverflow.com/questions/39818296/using-mysql-json-field-to-join-on-a-table
tbl_parts.doc_ids内のJSON配列の値と、tbl_docs.doc_idで結合してみる。
ポイントはJSON_CONTAINSを使っているところになる。
> SELECT
t1.part_id,
t2.original
FROM tbl_parts t1
JOIN tbl_docs t2
ON JSON_CONTAINS( t1.doc_ids, CAST(t2.doc_id AS JSON), '$');
+---------+-----------+
| part_id | original |
+---------+-----------+
| 1 | xxxxx.pdf |
| 1 | abc.xlsx |
| 4 | zzz.pdf |
+---------+-----------+
このテーブルの行をグループ化して、JSON_ARRAYAGGを使ってJSON配列に変換する
> SELECT
t1.part_id
,JSON_ARRAYAGG(t2.original) AS docs
FROM tbl_parts t1
JOIN tbl_docs t2
ON JSON_CONTAINS(t1.doc_ids, CAST(t2.doc_id AS JSON), '$')
GROUP BY part_id
+---------+---------------------------+
| part_id | docs |
+---------+---------------------------+
| 1 | ["xxxxx.pdf", "abc.xlsx"] |
| 4 | ["zzz.pdf"] |
+---------+---------------------------+
上記のテーブルをt3として、tbl_partsと結合する
SELECT
t1.part_id
,t3.docs
FROM tbl_parts t1
LEFT JOIN (
SELECT
t1.part_id
,JSON_ARRAYAGG(t2.original) AS docs
FROM tbl_parts t1
JOIN tbl_docs t2
ON JSON_CONTAINS(t1.doc_ids, CAST(t2.doc_id AS JSON), '$')
GROUP BY part_id
) t3
ON t1.part_id = t3.part_id;
+---------+---------------------------+
| part_id | docs |
+---------+---------------------------+
| 1 | ["xxxxx.pdf", "abc.xlsx"] |
| 2 | NULL |
| 3 | NULL |
| 4 | ["zzz.pdf"] |
| 5 | NULL |
| 6 | NULL |
+---------+---------------------------+
