MySQLやMariaDBでSELECT結果をJSONで取得する方法です。
最近はフロントとバックエンドを分けてAPI方式でJSONでデータを返すことも多いのでJSONで取得できると便利です。
サンプルデータ
下記のテーブルを使用してSQLでJSON取得します。
> SELECT * FROM food;
+----+------------+--------------+
| id | category | name |
+----+------------+--------------+
| 1 | vegetables | キャベツ |
| 2 | vegetables | レタス |
| 3 | vegetables | 人参 |
| 4 | vegetables | トマト |
| 5 | vegetables | スイカ |
| 6 | fruits | リンゴ |
| 7 | fruits | 桃 |
| 8 | fruits | イチゴ |
| 9 | fruits | ゴーヤ |
| 10 | fruits | メロン |
+----+------------+--------------+
10 rows in set (0.001 sec)
JSONで値を取得
レコードをJSONで取得する
レコードをJSONで取得します。
JSON_OBJECT([key, val[, key, val] …])を使用します。
> SELECT JSON_OBJECT("category", category, "name", name) AS food FROM food;
+----------------------------------------------------+
| food |
+----------------------------------------------------+
| {"category": "vegetables", "name": "キャベツ"} |
| {"category": "vegetables", "name": "レタス"} |
| {"category": "vegetables", "name": "人参"} |
| {"category": "vegetables", "name": "トマト"} |
| {"category": "vegetables", "name": "スイカ"} |
| {"category": "fruits", "name": "リンゴ"} |
| {"category": "fruits", "name": "桃"} |
| {"category": "fruits", "name": "イチゴ"} |
| {"category": "fruits", "name": "ゴーヤ"} |
| {"category": "fruits", "name": "メロン"} |
+----------------------------------------------------+
10 rows in set (0.001 sec)
category毎にJSONデータを配列で取得
category毎にJSONデータを配列で取得します。
GROUP BYでまとめて、値をJSON_ARRAYAGG()で配列にします。
ORDER BYを使用すると配列内の並び替えもできます。
> SELECT category, JSON_ARRAYAGG(JSON_OBJECT("name", name) ORDER BY id) AS foods FROM food GROUP BY category;
+------------+-----------------------------------------------------------------------------------------------------------------+
| category | foods |
+------------+-----------------------------------------------------------------------------------------------------------------+
| fruits | [{"name": "リンゴ"},{"name": "桃"},{"name": "イチゴ"},{"name": "ゴーヤ"},{"name": "メロン"}] |
| vegetables | [{"name": "キャベツ"},{"name": "レタス"},{"name": "人参"},{"name": "トマト"},{"name": "スイカ"}] |
+------------+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
まとめ
JSONでのデータのやり取りが多くなっているので、SQLでJSONを生成できるのは便利です。
コードもすっきりするので可読性も上がります。
もうSQLも1つの言語として履歴書に書いてもいいのでは？
ハイレベルでSQLが書けるってすごいのに、なぜか軽視されがちです。