MySQL JSON_CONTAINS Expressions

The `JSON_CONTAINS` expression in MySQL is used to determine whether a specified JSON document contains a given value or set of values. It returns `1` if the specified value is found in the JSON document, and `0` otherwise.

Usage

The `JSON_CONTAINS` expression is typically used when you need to verify the presence of specific data within a JSON document stored in a MySQL database. It is particularly useful for querying complex JSON structures to check for nested values.

JSON_CONTAINS(target, candidate[, path])

• target: The JSON document to be searched. Both `target` and `candidate` need to be valid JSON documents or strings that can be converted to JSON.
• candidate: The value or set of values to search for within the target. This can include complex data types such as numbers, booleans, and nulls, not just strings.
• path (optional): A JSON path specifying the location to search within the target document. If omitted, the function searches the entire JSON document.

Examples

1. Basic JSON Contains Check

SELECT JSON_CONTAINS('{"name": "John", "age": 30}', '"John"') AS result;

This example checks if the JSON document contains the string `"John"`, returning `1` as it is present.

2. Checking for Nested Values

SELECT JSON_CONTAINS('{"person": {"name": "John", "age": 30}}', '{"name": "John"}', '$.person') AS result;

Here, the expression verifies if the nested object within `person` contains the key-value pair `"name": "John"`, returning `1`.

3. Using JSON Path to Target Specific Array

SELECT JSON_CONTAINS('{"users": [{"name": "John"}, {"name": "Doe"}]}', '{"name": "Doe"}', '$.users') AS result;

`JSON_CONTAINS` checks if any object in the `users` array has the name `"Doe"`, returning `1`.

4. Checking for Numeric and Boolean Values

SELECT JSON_CONTAINS('{"active": true, "score": 100}', 'true', '$.active') AS result;

This example checks if the JSON document contains the boolean value `true` under the `active` key, returning `1`.

Tips and Best Practices

• Validate JSON documents. Ensure your JSON data is valid before using `JSON_CONTAINS` to avoid errors.
• Optimize with indexes. Consider indexing JSON columns for better performance when frequently searching for specific values.
• Use specific paths. Specify JSON paths to narrow down the search scope and improve query performance. If the specified path does not exist, the function returns `0`.
• Be aware of data types. Ensure that the data types of the candidate value match the data types in the JSON document to get accurate results.
• Handle special JSON data types. Be mindful of how `JSON_CONTAINS` handles arrays and objects, especially nested ones, to ensure correct query results.