【mysql5.6】JSON型でデータを取得する

mysql5.7から、jsonがサポートされました。
https://dev.mysql.com/doc/refman/5.7/en/json.html

しかし、mysql5.7以前のバージョンを使っているサービスは多く存在しているでしょう。
そこで、mysql5.7以前のバージョンでも、DBからJSON型でデータを取得する方法を記しておきます。

下記、サンプルになります。

サンプルテーブル

userテーブル

user_id	user_name	user_passwd	user_passwd_h1	user_passwd_h2	create_datetime
1	yamako	jdti8eF3	QP3J-Dhp	ZVSEg9Qe	2018/10/18 9:21:57.981
2	mikiko	6UU2hVxm	(NULL)	(NULL)	2018/10/18 12:05:18.452
3	yoshiko	mhKxekBR	FecFwETb	(NULL)	2018/10/18 18:58:31.216
4	nishiko	TJWeYTQb	2SapnkBt	TgdBKSLi	2018/10/17 15:43:12.754

ソース

CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT 'ユーザーID',
  `user_name` varchar(20) COLLATE utf8_bin NOT NULL COMMENT 'ユーザ名',
  `user_passwd` varchar(64) COLLATE utf8_bin NOT NULL COMMENT 'パスワード',
  `user_passwd_h1` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'パスワード1世代前',
  `user_passwd_h2` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'パスワード2世代前',
  `create_datetime` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '作成日時',
  PRIMARY KEY (`user_id`)
);

クエリでJSON型データを取得する

DBにクエリ発行し、JSON型でデータを取得する。
JSON型データというのは、key:value形式のデータを指しており、keyがカラム、valueがデータとなる。
なお、下記の項目を考慮する。
・データが文字列の場合、ダブルクォート"で囲う。
・データが数字、nullの場合、ダブルクォート"を付けない。
・カラムcreate_dataは、UNIXミリ秒で表示する。
・条件WHEREで指定した年月日を検索対象とする。

クエリ

SELECT
  CONCAT('{"accOffDateTime":', user_id, '.', ''), ','),
  CONCAT('"userName":', user_name, ','),
  CONCAT('"userPasswd":', user_passwd, '",'),
  case when user_passwd_h1 is null then '"userPasswdH1":null,' else CONCAT('"userPasswdH1":"', user_passwd_h1, '",') end,
  case when user_passwd_h2 is null then '"userPasswdH2":null,' else CONCAT('"userPasswdH2":"', user_passwd_h2, '",') end,
  CONCAT('"createDatetime":"', REPLACE(unix_timestamp(acc_on_datetime), '.', ''), '"}')
FROM
  user 
WHERE
  DATE_FORMAT(create_datetime, '%Y%m%d') = '20181018';

クエリ結果

{"userId":1, "userName":"yamako", "userPasswd":"jdti8eF3", "userPasswdH1":"QP3J-Dhp ", "userPasswdH2":"ZVSEg9Qe", "createDatetime":1539854517981}
{"userId":2, "userName":"mikiko", "userPasswd":"6UU2hVxm", "userPasswdH1":null, "userPasswdH2":null, "createDatetime":1539864318452}
{"userId":3, "userName":"yoshiko", "userPasswd":"mhKxekBR", "userPasswdH1":"FecFwETb", "userPasswdH2":null, "createDatetime":1539889111216}

参考ドキュメント

・CONCAT 関数
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_concat

・unix_timestamp 関数
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_unix-timestamp

・CASE 構文
https://dev.mysql.com/doc/refman/5.6/ja/case.html

・REPLACE 構文
https://dev.mysql.com/doc/refman/5.6/ja/replace.html

・DATE_FORMAT 関数
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_date-format