MySQL JSON

Summary: in this tutorial, you will learn how to use the MySQL JSON_VALID() function to validate JSON documents.

Introduction to the MySQL JSON_VALID() function

The JSON_VALID() function accepts a value and returns 1 if the value is a valid JSON document or 0 otherwise.

In MySQL, 1 means true and 0 means false because MySQL uses TINYINT(1) as the boolean type.

Here’s the syntax of the JSON_VALID() function:

JSON_VALID(value)Code language: SQL (Structured Query Language) (sql)

The JSON_VALID() returns NULL if the value is NULL.

In practice, you use the JSON_VALID() function to validate a JSON document before inserting it into the database.

MySQL JSON_VALID() function examples

The following example uses the JSON_VALID() function to determine whether a string is a valid JSON document or not.

SELECT JSON_VALID('{"name":"John Doe"}');Code language: SQL (Structured Query Language) (sql)

It returns 1 because the string is a valid JSON document:

+-----------------------------------+
| JSON_VALID('{"name":"John Doe"}') |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The following example also uses the JSON_VALID() function to check if a string is a valid JSON document:

SELECT JSON_VALID("{'name':'John Doe'}");Code language: SQL (Structured Query Language) (sql)

It returns 0 because the string is not a valid JSON document. The reason is that the string uses single quotes (‘), not double quotes (“) to wrap the key and value.

+-----------------------------------+
| JSON_VALID("{'name':'John Doe'}") |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the JSON_VALID() function to check if a value is a valid JSON document or not.

Was this tutorial helpful?