MySQL JSON_SEARCH Function: Complete Guide

The JSON_SEARCH function in MySQL is used to search for a specified value within JSON data and return the path of that value in the JSON.

The syntax is as follows:

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, wildcard_char[, path] ] ])

Description of parameters:

1. json_doc: JSON document to be searched.
2. one_or_all: this specifies the way of searching, it can be ‘one’ (only search for the first match) or ‘all’ (search for all matches)
3. search_str: the value to search
4. escape_char: The escape character used to escape special characters, default is backslash \.
5. Wildcard_char: The wildcard used for wildcard matching, defaulting to *
6. path: specifies the path to search in the JSON, default is NULL.

Suppose we have the following JSON data:

{"name": "John", "age": 30, "address": {"city": "New York", "zip": "10001"}}

One option is to use the JSON_SEARCH function to search for “New York”.

SELECT JSON_SEARCH('{"name": "John", "age": 30, "address": {"city": "New York", "zip": "10001"}}', 'one', 'New York');

The query will return “$.address.city,” indicating that “New York” was found in the JSON and its path is “$.address.city.”