JSON 値を検索する JSON 関数
このドキュメントでは、JSON 値を検索する JSON関数について説明します。
JSON_CONTAINS()
JSON_CONTAINS(json_doc, candidate [,path])関数は、 1または0返すことにより、指定されたcandidate JSON ドキュメントがターゲット JSON ドキュメント内に含まれているかどうかを示します。
例:
ここでa対象ドキュメントに含まれています。
SELECT JSON_CONTAINS('["a","b","c"]','"a"');
+--------------------------------------+
| JSON_CONTAINS('["a","b","c"]','"a"') |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.00 sec)
ここでe対象ドキュメントに含まれていません。
SELECT JSON_CONTAINS('["a","b","c"]','"e"');
+--------------------------------------+
| JSON_CONTAINS('["a","b","c"]','"e"') |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)
ここで{"foo": "bar"}対象ドキュメントに含まれています。
SELECT JSON_CONTAINS('{"foo": "bar", "aaa": 5}','{"foo": "bar"}');
+------------------------------------------------------------+
| JSON_CONTAINS('{"foo": "bar", "aaa": 5}','{"foo": "bar"}') |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
ここで、 "bar"対象ドキュメントのルートに含まれていません。
SELECT JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"');
+---------------------------------------------------+
| JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"') |
+---------------------------------------------------+
| 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)
ここで、 "bar"対象ドキュメントの$.foo属性に含まれています。
SELECT JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"', '$.foo');
+------------------------------------------------------------+
| JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"', '$.foo') |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_CONTAINS_PATH()
JSON_CONTAINS_PATH(json_doc, all_or_one, path [,path, ...])関数は、JSON ドキュメントに指定されたパスのデータが含まれているかどうかを示す0または1返します。
例:
ここで文書には$.foo含まれます。
SELECT JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo');
+--------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo') |
+--------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
ここでは文書に$.bar含まれていません。
SELECT JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.bar');
+--------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.bar') |
+--------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
ここで、ドキュメントには$.fooと$.aaa両方が含まれています。
SELECT JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo', '$.aaa');
+-----------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo', '$.aaa') |
+-----------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_EXTRACT()
JSON_EXTRACT(json_doc, path[, path] ...)関数は、 path引数に一致するドキュメントの部分から選択して、JSON ドキュメントからデータを抽出します。
SELECT JSON_EXTRACT('{"foo": "bar", "aaa": 5}', '$.foo');
+---------------------------------------------------+
| JSON_EXTRACT('{"foo": "bar", "aaa": 5}', '$.foo') |
+---------------------------------------------------+
| "bar" |
+---------------------------------------------------+
1 row in set (0.00 sec)
->
column->path関数は、 path引数に一致するcolumnのデータを返します。これはJSON_EXTRACT()のエイリアスです。
SELECT
j->'$.foo',
JSON_EXTRACT(j, '$.foo')
FROM (
SELECT
'{"foo": "bar", "aaa": 5}' AS j
) AS tbl;
+------------+--------------------------+
| j->'$.foo' | JSON_EXTRACT(j, '$.foo') |
+------------+--------------------------+
| "bar" | "bar" |
+------------+--------------------------+
1 row in set (0.00 sec)
->>
column->>path関数は、 path引数に一致するcolumnのデータを引用符なしで解除します。これはJSON_UNQUOTE(JSON_EXTRACT(doc, path_literal))の別名です。
SELECT
j->'$.foo',
JSON_EXTRACT(j, '$.foo')
j->>'$.foo',
JSON_UNQUOTE(JSON_EXTRACT(j, '$.foo'))
FROM (
SELECT
'{"foo": "bar", "aaa": 5}' AS j
) AS tbl;
+------------+--------------------------+-------------+----------------------------------------+
| j->'$.foo' | JSON_EXTRACT(j, '$.foo') | j->>'$.foo' | JSON_UNQUOTE(JSON_EXTRACT(j, '$.foo')) |
+------------+--------------------------+-------------+----------------------------------------+
| "bar" | "bar" | bar | bar |
+------------+--------------------------+-------------+----------------------------------------+
1 row in set (0.00 sec)
JSON_KEYS()
JSON_KEYS(json_doc [,path])関数は、 pathオブジェクトの最上位キーを JSON 配列として返します。3 引数が指定されている場合は、選択したパスから最上位キーを返します。
例:
次の例では、JSON ドキュメント内の 2 つの最上位キーを返します。
SELECT JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}');
+---------------------------------------------------------------------------+
| JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}') |
+---------------------------------------------------------------------------+
| ["name", "type"] |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
次の例では、JSON ドキュメントの$.nameパスにある最上位キーを返します。
SELECT JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}', '$.name');
+-------------------------------------------------------------------------------------+
| JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}', '$.name') |
+-------------------------------------------------------------------------------------+
| ["first", "last"] |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_検索()
JSON_SEARCH(json_doc, one_or_all, str)関数は、JSON ドキュメントで文字列の 1 つまたはすべての一致を検索します。
例:
次の例では、 a配列のインデックス 2 の位置にあるccの最初の結果を検索できます。
SELECT JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','one','cc');
+------------------------------------------------------------------------+
| JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','one','cc') |
+------------------------------------------------------------------------+
| "$.a[2]" |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
ここで同じことを行いますが、最初の結果だけでなくすべての結果を取得するには、 one_or_allをallに設定します。
SELECT JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','all','cc');
+------------------------------------------------------------------------+
| JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','all','cc') |
+------------------------------------------------------------------------+
| ["$.a[2]", "$.b[0]"] |
+------------------------------------------------------------------------+
1 row in set (0.01 sec)
メンバー()
str MEMBER OF (json_array)関数は渡された値strがjson_arrayの要素であるかどうかをテストし、 1返します。そうでない場合は0返します。引数のいずれかがNULLの場合はNULL返します。
SELECT '🍍' MEMBER OF ('["🍍","🥥","🥭"]') AS 'Contains pineapple';
+--------------------+
| Contains pineapple |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
JSON_OVERLAPS()
JSON_OVERLAPS(json_doc, json_doc)関数は、2 つの JSON ドキュメントに重複部分があるかどうかを示します。重複している場合は1返します。重複していない場合は0返します。引数のいずれかがNULLの場合はNULL返します。
例:
次の例では、配列値の要素数が同じではないため、重複がないことがわかります。
SELECT JSON_OVERLAPS(
'{"languages": ["Go","Rust","C#"]}',
'{"languages": ["Go","Rust"]}'
) AS 'Overlaps';
+----------+
| Overlaps |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
次の例は、両方の JSON ドキュメントが同一であるため重複していることを示しています。
SELECT JSON_OVERLAPS(
'{"languages": ["Go","Rust","C#"]}',
'{"languages": ["Go","Rust","C#"]}'
) AS 'Overlaps';
+----------+
| Overlaps |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
次の例では、重複があり、2 番目のドキュメントに追加の属性があることを示しています。
SELECT JSON_OVERLAPS(
'{"languages": ["Go","Rust","C#"]}',
'{"languages": ["Go","Rust","C#"], "arch": ["arm64"]}'
) AS 'Overlaps';
+----------+
| Overlaps |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)