MariaDBでJSONを扱う場合

1. はじめに

MariaDBでちょっと複雑なjsonを作成するとダブルクォーテーションにバックスラッシュが付与されてしまい、正しくjsonとして扱われないことがある。
以下の方法はマニュアルに記載されていないのでうまくいかないパターンがあるかもしれませんが参考までに記載します。

2. JSONでバックスラッシュが発生する例

MariaDB [test]> SELECT JSON_OBJECT('a', CONCAT('[', JSON_OBJECT('b', 'c'), ']'));
+-----------------------------------------------------------+
| JSON_OBJECT('a', CONCAT('[', JSON_OBJECT('b', 'c'), ']')) |
+-----------------------------------------------------------+
| {"a": "[{\"b\": \"c\"}]"}                                 |
+-----------------------------------------------------------+
1 row in set (0.000 sec)

3. JSON_COMPACTを使用

MariaDBのマニュアルに記載されていないが、JSON_COMPACTを使用すればダブルクォーテーションにバックスラッシュを消すことが可能。

MariaDB [test]> SELECT JSON_OBJECT('a', JSON_COMPACT(CONCAT('[', JSON_OBJECT('b', 'c'), ']')));
+-------------------------------------------------------------------------+
| JSON_OBJECT('a', JSON_COMPACT(CONCAT('[', JSON_OBJECT('b', 'c'), ']'))) |
+-------------------------------------------------------------------------+
| {"a": [{"b": "c"}]}                                                     |
+-------------------------------------------------------------------------+
1 row in set (0.001 sec)

4. もう少し複雑な例

SELECT
    't' AS t,
    JSON_OBJECT(
        'a', "a",
        'b', "b",
        'c', (
            SELECT
                JSON_OBJECT(
                    'c.1', 'c1',
                    'c.2', UPPER('c2'),
                    'c.3', UPPER(
                        (
                            SELECT
                                JSON_OBJECT(
                                    'c.3.1', 'c31',
                                    'c.3.2', 'c32',
                                    'c.3.3', (
                                            SELECT
                                                JSON_OBJECT('c.4.1', 'c41',
                                                            'c.4.2', 'c42')
                                        )
                                )
                        )
                    )
                )
        )
    ) as col;

sql実行結果

+---+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | col                                                                                                                                                           |
+---+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | {"a": "a", "b": "b", "c": {"c.1": "c1", "c.2": "C2", "c.3": "{\"C.3.1\": \"C31\", \"C.3.2\": \"C32\", \"C.3.3\": {\"C.4.1\": \"C41\", \"C.4.2\": \"C42\"}}"}} |
+---+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

JSON_COMPACTを設定する

SELECT
    't' AS t,
    JSON_OBJECT(
        'a', 'a',
        'b', 'b',
        'c', JSON_COMPACT(
            (
                SELECT
                    JSON_COMPACT(
                        JSON_OBJECT(
                            'c.1', 'c1',
                            'c.2', UPPER('c2'),
                            'c.3', JSON_COMPACT(
                                UPPER(
                                    (
                                        SELECT
                                            JSON_OBJECT(
                                                'c.3.1', 'c31',
                                                'c.3.2', 'c32',
                                                'c.3.3', JSON_COMPACT(
                                                    (
                                                        SELECT
                                                            JSON_OBJECT('c.4.1', 'c41',
                                                                        'c.4.2', 'c42')
                                                    )
                                                )
                                            )
                                    )
                                )
                            )
                        )
                    )
            )
        )
    ) as col;

+---+-------------------------------------------------------------------------------------------------------------------------------------------+
| t | col                                                                                                                                       |
+---+-------------------------------------------------------------------------------------------------------------------------------------------+
| t | {"a": "a", "b": "b", "c": {"c.1": "c1", "c.2": "C2", "c.3": {"c.3.1": "c31", "c.3.2": "c32", "c.3.3": {"c.4.1": "c41", "c.4.2": "c42"}}}} |
+---+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

参考にしたURL
MariaDB の JSON_SET, JSON_INSERT, JSON_REPLACE とかで JSON を使う方法