mysql で json データを検索

mysql で json データを検索する方法のメモ。

■テーブル定義

create table json5 (
  id       varchar(16) not null,
  obj      json,

  primary key (id)
);

■データ登録

insert into json5 set
 id = '01',
 obj = '{"id": "01", "items": [{"name": "name01_01", "value": "value01_01"}, {"name": "name01_02", "value": "value01_02"}]}'
;

insert into json5 set
 id = '02',
 obj = '{"id": "02", "items": [{"name": "name02_01", "value": "value02_01"}, {"name": "name02_02", "value": "value02_02"}, {"name": "name02_03", "value": "value02_03"}]}'
;

■検索
以下では、json_search() を使って検索しています。
json_search() には以下のようにパラメータを指定します。
json_search(json_doc, one_or_all, search_str[, escape_char[, path] ...)

select
  json_pretty(obj)
from
  json5
where
  json_search(obj, 'one', 'name01_02', null, '$.items[*].name')
\G

■検索結果

*************************** 1. row ***************************
json_pretty(obj): {
  "id": "01",
  "items": [
    {
      "name": "name01_01",
      "value": "value01_01"
    },
    {
      "name": "name01_02",
      "value": "value01_02"
    }
  ]
}