MySQLのjson型の検索メモ

MySQLのjson型カラムの値での検索を試してみた。

準備

テーブル定義

CREATE TABLE `json_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `item` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

データ

INSERT INTO `json_test` (`id`, `item`)
VALUES
    (1, '{\"key\": 1, \"value\": \"AAAA\"}'),
    (2, '{\"key\": 10, \"child\": {\"key\": 100, \"value\": \"XXXXX\"}, \"value\": \"AAAA\"}'),
    (3, '{\"items\": [{\"key\": 1, \"value\": \"AAAA\"}, {\"key\": 2, \"value\": \"BBBBB\"}]}'),
    (4, '[{\"key\": 4, \"value\": \"CCCCC\"}, {\"key\": 5, \"value\": \"DDDDD\"}]');

オブジェクトを検索

対象データ

{
  "key": 1,
  "value": "AAAA"
}

SELECT * FROM json_test WHERE item->"$.key" = 1;

文字列の場合

SELECT * FROM json_test WHERE item->"$.value" = "AAAA";

オブジェクト配下のオブジェクトを検索

{
  "key": 10,
  "value": "AAAA",
  "child": {
    "key": 100,
    "value": "XXXXX"
  }
}

SELECT * FROM json_test WHERE item->"$.child.key" = 100;

配列を検索

[
  {
    "key": 4,
    "value": "CCCCC"
  },
  {
    "key": 5,
    "value": "DDDDD"
  }
]

SELECT * FROM json_test WHERE json_contains(item->"$[*].key", '4');

文字列の場合

SELECT * FROM json_test WHERE json_contains(item->"$[*].value", '"DDDDD"');

オブジェクト配下の配列を検索

{
  "items": [
    {
      "key": 1,
      "value": "AAAA"
    },
    {
      "key": 2,
      "value": "BBBBB"
    }
  ]
}

SELECT * FROM json_test WHERE json_contains(item->"$.items[*].key", '2');

おまけ

同様にselect句でjsonの要素を取得可能。

mysql> SELECT item->"$.child.value" FROM json_test WHERE item->"$.child.key" = 100;
+-----------------------+
| item->"$.child.value" |
+-----------------------+
| "XXXXX"               |
+-----------------------+

ただ、下記のケースで DDDDD だけをselectするにはどうしたらよいのだろうか。

mysql> SELECT item->"$[*].value" FROM json_test WHERE json_contains(item->"$[*].value", '"DDDDD"');
+--------------------+
| item->"$[*].value" |
+--------------------+
| ["CCCCC", "DDDDD"] |
+--------------------+