How to Use JSON_CONTAINS in MySQL: Syntax and Examples

Use the JSON_CONTAINS(json_doc, val[, path]) function in MySQL to check if json_doc contains the specified val. It returns 1 if found, otherwise 0. The optional path lets you specify where to look inside the JSON document.

Syntax

The JSON_CONTAINS function checks if a JSON document contains a given value or object.

• json_doc: The JSON document to search.
• val: The JSON value or object to find inside json_doc.
• path (optional): A JSON path expression to specify where to search inside json_doc. Defaults to the whole document.

The function returns 1 if val is found, otherwise 0.

sql

JSON_CONTAINS(json_doc, val[, path])

Example

This example shows how to use JSON_CONTAINS to check if a JSON array contains a specific value and how to check inside a nested JSON object using the path argument.

sql

SELECT JSON_CONTAINS('["apple", "banana", "cherry"]', '"banana"') AS contains_banana,
       JSON_CONTAINS('{"fruits": ["apple", "banana", "cherry"]}', '"banana"', '$.fruits') AS contains_banana_in_fruits;

-- Output explanation:
-- contains_banana returns 1 because "banana" is in the array.
-- contains_banana_in_fruits returns 1 because "banana" is inside the 'fruits' array in the JSON object.

Output

contains_banana | contains_banana_in_fruits
--------------- | -----------------------
1               | 1

Common Pitfalls

Common mistakes when using JSON_CONTAINS include:

• Not quoting string values properly inside val. For example, searching for a string must be wrapped in double quotes inside single quotes: '"value"'.
• For numeric or boolean values, quotes are not needed.
• Using incorrect JSON path syntax in the path argument.
• Expecting JSON_CONTAINS to work like a simple substring search; it checks JSON structure, not text.

Example of wrong and right usage:

sql

-- Wrong: searching for string without quotes
SELECT JSON_CONTAINS('["apple", "banana"]', 'banana');

-- Right: string value properly quoted
SELECT JSON_CONTAINS('["apple", "banana"]', '"banana"');

Output

NULL
1

Quick Reference

Parameter	Description	Example
json_doc	The JSON document to search	'["a", "b", "c"]'
val	The JSON value or object to find	'"b"' or '123' or '{"key": "value"}'
path (optional)	JSON path to specify search location	'$.key' or '$.array[0]'

Key Takeaways

Use JSON_CONTAINS to check if a JSON document contains a specific value or object.

Always quote string values inside the val argument with double quotes inside single quotes.

The optional path argument lets you search inside nested JSON structures.

JSON_CONTAINS returns 1 if found, otherwise 0.

Incorrect quoting or path syntax causes unexpected results or errors.