How to search a string in a JSON (JavaScript Object Notation) value using the JSON_SEARCH() function?

JSON_SEARCH(json, one_or_all, pattern, escape, path) is a MySQL built-in function that searches for a string pattern in a JSON (JavaScript Object Notation) value. It returns a single match as a JSON string, or multiple matches as a JSON array. String pattern supports 2 wildcard characters: "_" for any character and "%" for any number of characters. For example:

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

SELECT JSON_SEARCH(@j, 'one', 'abc');
  -- +-------------------------------+
  -- | JSON_SEARCH(@j, 'one', 'abc') |
  -- +-------------------------------+
  -- | "$[0]"                        |
  -- +-------------------------------+

SELECT JSON_SEARCH(@j, 'all', 'abc');
  -- +-------------------------------+
  -- | JSON_SEARCH(@j, 'all', 'abc') |
  -- +-------------------------------+
  -- | ["$[0]", "$[2].x"]            |
  -- +-------------------------------+

SELECT JSON_SEARCH(@j, 'all', 'ghi');
  -- +-------------------------------+
  -- | JSON_SEARCH(@j, 'all', 'ghi') |
  -- +-------------------------------+
  -- | NULL                          |
  -- +-------------------------------+

SELECT JSON_SEARCH(@j, 'all', '10');
  -- +------------------------------+
  -- | JSON_SEARCH(@j, 'all', '10') |
  -- +------------------------------+
  -- | "$[1][0].k"                  |
  -- +------------------------------+

SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
  -- +-----------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '10', NULL, '$') |
  -- +-----------------------------------------+
  -- | "$[1][0].k"                             |
  -- +-----------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
  -- +--------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
  -- +--------------------------------------------+
  -- | "$[1][0].k"                                |
  -- +--------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
  -- +---------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
  -- +---------------------------------------------+
  -- | "$[1][0].k"                                 |
  -- +---------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
  -- +-------------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
  -- +-------------------------------------------------+
  -- | "$[1][0].k"                                     |
  -- +-------------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
  -- +--------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
  -- +--------------------------------------------+
  -- | "$[1][0].k"                                |
  -- +--------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
  -- +-----------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
  -- +-----------------------------------------------+
  -- | "$[1][0].k"                                   |
  -- +-----------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
  -- +---------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
  -- +---------------------------------------------+
  -- | "$[2].x"                                    |
  -- +---------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '%a%');
  -- +-------------------------------+
  -- | JSON_SEARCH(@j, 'all', '%a%') |
  -- +-------------------------------+
  -- | ["$[0]", "$[2].x"]            |
  -- +-------------------------------+

SELECT JSON_SEARCH(@j, 'all', '%b%');
  -- +-------------------------------+
  -- | JSON_SEARCH(@j, 'all', '%b%') |
  -- +-------------------------------+
  -- | ["$[0]", "$[2].x", "$[3].y"]  |
  -- +-------------------------------+

SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
  -- +---------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
  -- +---------------------------------------------+
  -- | "$[0]"                                      |
  -- +---------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
  -- +---------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
  -- +---------------------------------------------+
  -- | "$[2].x"                                    |
  -- +---------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
  -- +---------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
  -- +---------------------------------------------+
  -- | NULL                                        |
  -- +---------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
  -- +-------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
  -- +-------------------------------------------+
  -- | NULL                                      |
  -- +-------------------------------------------+

SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
  -- +-------------------------------------------+
  -- | JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
  -- +-------------------------------------------+
  -- | "$[3].y"                                  |
  -- +-------------------------------------------+

Reference information of the JSON_SEARCH() function:

JSON_SEARCH(json, one_or_all, pattern, escape, path): match
  Searches for a string pattern in a JSON (JavaScript Object Notation) value.
  It returns a single match as a JSON string, or multiple matches
  as a JSON array. String pattern supports 2 wildcard characters:
  "_" for any character and "%" for any number of characters.

Arguments, return value and availability:
  json: Required. The JSON value to be search from.
  one_or_all. Required. The search option: "one" or "all".
  pattern: Required. The string pattern to search for.
  escape: Optional. The default is NULL.
    The escape character to protect the wildcard characters: _ and %
  path: Optional. The default is "$". The JSON path to limit the search.
  match: Return value. The match result.
  Available since MySQL 5.7.

⇒ JSON_SET() - Inserting/updating JSON Child Members

⇐ JSON_SCHEMA_VALIDATION_REPORT() - JSON Schema Validation Report

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References