MySQL 5.7でJSON_OBJECTでハマった話

MySQL 5.7から導入されたJSON_OBJECTがめっちゃ便利！と思ってたら、
ちょっと躓いてしまったことがあったのでメモ。

JSON_OBJECTしたものをGROUP_CONCATやCONCATして、それをJSON_OBJECTにしたい、って時。

たとえばこういうテーブルがあったとして、

create_data.sql

CREATE TABLE menus (occasion varchar(10),menu varchar(10));
INSERT INTO menus(occasion,menu) VALUE('朝','照り焼き');
INSERT INTO menus(occasion,menu) VALUE('昼','棒々鶏');
INSERT INTO menus(occasion,menu) VALUE('夜','唐揚げ');

こうしたい

{"menus": [{"朝": "照り焼き"},
           {"昼": "棒々鶏"},
           {"夜": "唐揚げ"}]
}

で、こういう感じで書いてみた。

first_try.sql

SELECT
    JSON_OBJECT("menus", menus)
FROM(
    SELECT
        CONCAT(
            '[',
            GROUP_CONCAT(JSON_OBJECT(occasion, menu))
            ,']'
        ) AS menus
    FROM
        menus
) AS m;
------------------------------------------
result ->
{"menus": "[{\"朝\": \"照り焼き\"},{\"昼\": \"棒々鶏\"},{\"夜\": \"唐揚げ\"}]"}

はじめに作ったJSON_OBJECTの"とかがエスケープされてしまっとる。。。

結論から書くと、CASTで型変換してあげなきゃいけないみたい。

second_try.sql

SELECT
    JSON_OBJECT("menus", CAST(menus AS JSON))
FROM(
    SELECT
        CONCAT(
            '[',
            GROUP_CONCAT(JSON_OBJECT(occasion, menu))
            ,']'
        ) AS menus
    FROM
        menus
) AS m;
------------------------------------------
result ->
{"menus": [{"朝": "照り焼き"}, {"昼": "棒々鶏"}, {"夜": "唐揚げ"}]}

順序だてて説明。

SELECT JSON_OBJECT('id', 87, 'name', 'carrot');

result -> {"id": 87, "name": "carrot"} // JSON_OBJECTだけだと、JSON型

SELECT JSON_OBJECT("human",JSON_OBJECT('id', 87, 'name', 'carrot'));

result -> {"human": {"id": 87, "name": "carrot"}} // JSONのネストがちゃんとできる

でも、CONCATもしくはGROUP CONCATしてしまうと

SELECT CONCAT(JSON_OBJECT('id', 87, 'name', 'carrot'));

result -> {"id": 87, "name": "carrot"} // 文字列型になっちゃうっぽい

SELECT JSON_OBJECT("human",CONCAT(JSON_OBJECT('id', 87, 'name', 'carrot')));

result -> {"human": "{\"id\": 87, \"name\": \"carrot\"}"} // 文字列と認識されるのでエスケープされちゃう

じゃあ、どうするか？

→CASTで型をJSONにしてやったらいいっぽい

SELECT JSON_OBJECT("human",CAST(CONCAT(JSON_OBJECT('id', 87, 'name', 'carrot')) AS JSON));

result -> {"id": 87, "name": "carrot"} // うまくいった。

ちょっと見にくいですが、

CAST(
    CONCAT(
        JSON_OBJECT('id', 87, 'name', 'carrot')
    ) AS JSON
)

ってことです。