How to Use JSON_SEARCH in MySQL: Syntax and Examples

Use JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) in MySQL to find the path of a string inside a JSON document. It returns the path to the first or all occurrences of the string or NULL if not found.

Syntax

The JSON_SEARCH function searches for a string inside a JSON document and returns the path to the matching element.

• json_doc: The JSON document to search.
• one_or_all: Use 'one' to find the first match or 'all' to find all matches.
• search_str: The string value to search for.
• escape_char (optional): Character to escape wildcards in search_str.
• path (optional): JSON path expressions to limit the search area.

sql

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

Example

This example shows how to find the path of the string 'apple' inside a JSON array stored in a table.

sql

CREATE TABLE fruits (id INT, data JSON);

INSERT INTO fruits VALUES
(1, '["apple", "banana", "cherry"]'),
(2, '{"type": "apple", "color": "red"}');

SELECT id, JSON_SEARCH(data, 'one', 'apple') AS path_found FROM fruits;

Output

+----+------------+
| id | path_found |
+----+------------+
|  1 | $[0]       |
|  2 | $.type     |
+----+------------+

Common Pitfalls

Common mistakes when using JSON_SEARCH include:

• Not specifying one or all correctly, which affects the result type.
• Expecting the function to return the value instead of the path.
• Not using proper JSON paths to narrow the search, causing unexpected results.
• Using JSON_SEARCH on non-JSON columns or invalid JSON data.

sql

/* Wrong: Missing 'one' or 'all' argument */
SELECT JSON_SEARCH(data, 'apple') FROM fruits;

/* Right: Specify 'one' or 'all' */
SELECT JSON_SEARCH(data, 'one', 'apple') FROM fruits;

Quick Reference

Parameter	Description
json_doc	JSON document to search
one_or_all	'one' for first match, 'all' for all matches
search_str	String to find inside JSON
escape_char	Optional escape character for wildcards
path	Optional JSON path(s) to limit search

Key Takeaways

Use JSON_SEARCH to find the path of a string inside JSON data in MySQL.

Specify 'one' or 'all' to control whether you want the first or all matches.

The function returns JSON paths, not the values themselves.

Use JSON paths to narrow your search for better performance and accuracy.

Ensure your data is valid JSON before using JSON_SEARCH.