MySQL JSON_VALID() Function

The `JSON_VALID()` function in MySQL checks whether a JSON document is valid. It returns `1` if the JSON is valid and `0` if it is not.

Usage

The `JSON_VALID()` function is used to validate JSON documents stored in a database, ensuring they adhere to proper JSON formatting. It is particularly useful for data integrity checks before performing operations on JSON fields. Note that `JSON_VALID()` checks only for syntactic validity and does not assess semantic correctness, such as logical errors within the JSON content.

JSON_VALID(json_doc)

In this syntax, `json_doc` is the JSON document you want to validate.

Examples

1. Basic Validation

SELECT JSON_VALID('{"name": "John", "age": 30}');

This example checks if the JSON string is valid and returns `1` since the JSON is correctly formatted.

2. Invalid JSON Check

SELECT JSON_VALID('{"name": "John, "age": 30}');

Here, the function returns `0` because the JSON string is invalid due to a missing quote after `"John"`.

3. Validation in a Table

SELECT id, data, JSON_VALID(data) AS is_valid
FROM json_table;

In this example, each row's JSON data in the `json_table` is validated, with the results displayed in a new column named `is_valid`.

Tips and Best Practices

• Validate before storing. Always use `JSON_VALID()` to check JSON data before inserting or updating to ensure data integrity.
• Use with conditional statements. Combine `JSON_VALID()` with `WHERE` clauses to filter out invalid JSON records during data retrieval.
• Optimize data handling. Regularly validate JSON fields to prevent issues in applications relying on JSON data. Be mindful of performance implications when validating large JSON documents.
• Error identification. While `JSON_VALID()` only returns a boolean, use it in conjunction with error logging to identify and correct invalid JSON entries.
• Compatibility note. `JSON_VALID()` was introduced in MySQL 5.7.8. Ensure your version supports this function if working on older MySQL versions.