The JSON_VALID function in MySQL is used to check whether a given string is a valid JSON document or not. This function is particularly useful when you need to ensure that the data stored in a column with the JSON data type is well-formed JSON.

Syntax

Here is the syntax for the JSON_VALID function:

JSON_VALID(json_document)

json_document: The JSON document or expression to be validated.

The function returns 1 if the provided JSON document is valid, and 0 otherwise.

Example

Here’s an example of how to use the JSON_VALID function:

-- Create a table with a JSON column
CREATE TABLE user_data (
    id INT PRIMARY KEY,
    data TEXT
);

-- Insert valid JSON data
INSERT INTO user_data VALUES 
(1, '{"name": "John", "age": 30, "city": "New York"}');

-- Insert invalid JSON data
INSERT INTO user_data VALUES 
(2, '{"name": "Alice", "age": 25, "city": "Los Angeles", "');

-- Check if the JSON data is valid
SELECT id, JSON_VALID(data) AS is_valid FROM user_data;

In this example, the first INSERT statement inserts a valid JSON document into the data column of the user_data table. The second INSERT statement attempts to insert an invalid JSON document by ending it prematurely with a double quote. Finally, the SELECT statement uses the JSON_VALID function to check the validity of the JSON data for each row in the table.

The result of the SELECT statement will show that the first row has a value of 1 for the is_valid column, indicating that the JSON data is valid. The second row will have a value of 0, indicating that the JSON data is not valid.