皆さん、こんにちは。エンジニアのHatanoです。 業務において、データを集計する際に、「テキスト型のカラムにおいて、配列として処理できるテキストが入っていれば配列の各要素、処理できないテキストの場合はnullを取得したい」という場面がありました。 今回は、この集計を行うために必要な、「mysqlで、JSONテキストから型名を取り出す」方法について紹介します。

実行環境

以下の環境で実行及び動作確認を行っています。

MySQL [test]> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.45    |
+-----------+

解説

テキストが配列かどうかを判定するには、JSON_TYPEメソッドを使います。 このメソッドは、引数のJSON値のデータ型を文字列として返します。返り値は、以下の種類があります。

ARRAY, OBJECT, BOOLEAN, DOUBLE, INTEGER, NULL, STRING

また、引数のデータがJSON値として処理できない場合は、引数がNULLならNULL値が返され、それ以外ならエラーが発生します。

以下のtestsテーブルを用いて、具体例を見ていきます。

CREATE TABLE tests
(
id int NOT NULL AUTO_INCREMENT,
target text,
PRIMARY KEY (id)
);

INSERT INTO tests (id,target) VALUES
(1,'[1, 2, {"key": "value"}]'),
(2,'{"key":"value"}'),
(3,'true'),
(4,'1.2'),
(5,'1'),
(6,'null'),
(7,'"a sample string"'),
(8,NULL),
(9,'["a","g!",[1,2],345,"test_sentence_3"]')

JSON_TYPEメソッドを用いることで、各レコードのデータ型を取得できます。

SELECT id,target,JSON_TYPE(target) as 'json_type' from tests;
+----+----------------------------------------+-----------+
| id | target                                 | json_type |
+----+----------------------------------------+-----------+
|  1 | [1, 2, {"key": "value"}]               | ARRAY     |
|  2 | {"key":"value"}                        | OBJECT    |
|  3 | true                                   | BOOLEAN   |
|  4 | 1.2                                    | DOUBLE    |
|  5 | 1                                      | INTEGER   |
|  6 | null                                   | NULL      |
|  7 | "a sample string"                      | STRING    |
|  8 | NULL                                   | NULL      |
|  9 | ["a","g!",[1,2],345,"test_sentence_3"] | ARRAY     |
+----+----------------------------------------+-----------+

json_typeにJSON値として認識できない引数を与えた場合、次のようなエラーとなります。

select json_type('ABC');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.

なお、json_type('null')の返り値は'NULL'という文字列であり、引数がNULLの場合の返り値(NULL値)とは異なります。

select json_type('null') is NULL, json_type(NULL) is NULL;
+---------------------------+-------------------------+
| json_type('null') is NULL | json_type(NULL) is NULL |
+---------------------------+-------------------------+
|                         0 |                       1 |
+---------------------------+-------------------------+

使用例

json_typeをwhereで絞り込むことで、データ型での絞り込みが可能となります。

SELECT id,target,JSON_TYPE(target) as 'json_type' from tests where JSON_TYPE(target) = 'ARRAY';
+----+----------------------------------------+-----------+
| id | target                                 | json_type |
+----+----------------------------------------+-----------+
|  1 | [1, 2, {"key": "value"}]               | ARRAY     |
|  9 | ["a","g!",[1,2],345,"test_sentence_3"] | ARRAY     |
+----+----------------------------------------+-----------+

おわりに

様々な形式のテキストが同じカラムに挿入される場合、データ型の取得は必要不可欠です。mysqlを使っている方は、ぜひ試してみてください。