MySQLでJSONカラムを利用する(実践編その1)

前回
MySQLでJSONカラムを利用する(備忘録)

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                      |
+---------+---------------------------+