Collections:
JSON_SEARCH() - Searching String in JSON
How to search a string in a JSON (JavaScript Object Notation) value using the JSON_SEARCH() function?
✍: FYIcenter.com
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
2023-12-11, 225🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
How To Create a Table Index in Oracle? If you have a table with a lots of rows, and you know that on...
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...