JSON_SEARCH

説明

JSON_SEARCH 関数は JSON ドキュメント内の指定した値を検索します。一致した場合は値へのパスを返し、一致しない場合は NULL を返します。JSON の階層構造を再帰的に検索できます。

構文

JSON_SEARCH( <json_object>, <one_or_all>, <search_value> )

パラメータ

• <json_object>: 検索対象の JSON 型ドキュメントです。
• <one_or_all>: 一致した値のうち、最初の一件だけを返すか、すべてを返すかを指定する文字列です。'one' または 'all' を指定します。
• <search_value>: 検索する文字列です。 '%' は任意の長さの文字列、'_' は任意の一文字に一致するワイルドカードとして使用できます。

戻り値

Nullable(JSON): 戻り値は <one_or_all> によって次のように変わります。

1. 'one': 一致する値があれば、その値を指す JSON パスを返します。一致しなければ NULL を返します。
2. 'all': 一致するすべての値のパスを返します。複数ある場合は JSON 配列として返し、一致しなければ NULL を返します。

注意事項

• one_or_all が 'one' の場合は最初に一致したパス、'all' の場合は一致したすべてのパスを返します。それ以外の値はエラーになります。
• 一致する値がない場合は NULL を返します。
• <json_object>、<one_or_all>、<search_value> のいずれかが NULL の場合は NULL を返します。

例

1. 単一の値を検索する (one)

   SELECT JSON_SEARCH('{"name": "John", "age": 30}', 'one', 'John');

   +-----------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30}', 'one', 'John') |
   +-----------------------------------------------------------+
   | "$.name"                                                  |
   +-----------------------------------------------------------+
   

2. 複数の値が一致しても、one ではパスを一件だけ返す

   SELECT JSON_SEARCH('{"name": "John", "age": 30, "alias": "John"}', 'one', 'John');

   +----------------------------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30, "alias": "John"}', 'one', 'John') |
   +----------------------------------------------------------------------------+
   | "$.name"                                                                   |
   +----------------------------------------------------------------------------+

3. 一致するすべての値を検索する (all)

   SELECT JSON_SEARCH('{"name": "John", "age": 30, "alias": "John"}', 'all', 'John');

   +----------------------------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30, "alias": "John"}', 'all', 'John') |
   +----------------------------------------------------------------------------+
   | ["$.alias","$.name"]                                                       |
   +----------------------------------------------------------------------------+

4. '%' をワイルドカードとして使用する

   SELECT JSON_SEARCH('{"name": "John", "age": 30, "alias": "John"}', 'all', '%');

   +-------------------------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30, "alias": "John"}', 'all', '%') |
   +-------------------------------------------------------------------------+
   | ["$.alias","$.name"]                                                    |
   +-------------------------------------------------------------------------+

5. '_' をワイルドカードとして使用する

   SELECT JSON_SEARCH('{"name": "John", "age": 30, "alias": "Jihn"}', 'all', 'J_hn');

   +----------------------------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30, "alias": "Jihn"}', 'all', 'J_hn') |
   +----------------------------------------------------------------------------+
   | ["$.alias","$.name"]                                                       |
   +----------------------------------------------------------------------------+

6. ワイルドカードをエスケープする

   SELECT JSON_SEARCH('{"name": "John", "age": 30, "alias": "J_hn"}', 'all', 'J\_hn');

   +-----------------------------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30, "alias": "J_hn"}', 'all', 'J\_hn') |
   +-----------------------------------------------------------------------------+
   | "$.alias"                                                                   |
   +-----------------------------------------------------------------------------+

   'J_hn' は "J_hn" にのみ一致し、"John" には一致しません。

7. 一致する値がない場合

   SELECT JSON_SEARCH('{"name": "John", "age": 30}', 'one', 'Alice');

   +------------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30}', 'one', 'Alice') |
   +------------------------------------------------------------+
   | NULL                                                       |
   +------------------------------------------------------------+

8. NULL パラメータ

   SELECT JSON_SEARCH('{"name": "John", "age": 30}', NULL, 'Alice');

   +-----------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30}', NULL, 'Alice') |
   +-----------------------------------------------------------+
   | NULL                                                      |
   +-----------------------------------------------------------+

   SELECT JSON_SEARCH('{"name": "John", "age": 30}', 'one', NULL);

   +---------------------------------------------------------+
   | JSON_SEARCH('{"name": "John", "age": 30}', 'one', NULL) |
   +---------------------------------------------------------+
   | NULL                                                    |
   +---------------------------------------------------------+

   SELECT JSON_SEARCH(NULL, 'one', 'Alice');

   +-----------------------------------+
   | JSON_SEARCH(NULL, 'one', 'Alice') |
   +-----------------------------------+
   | NULL                              |
   +-----------------------------------+

9. 無効な <one_or_all> パラメータ

   SELECT JSON_SEARCH('{"name": "John", "age": 30}', 'three', 'Alice');

   ERROR 1105 (HY000): errCode = 2, detailMessage = [INVALID_ARGUMENT]the one_or_all argument three is not 'one' not 'all'