Menu

MariaDB JSON_VALID(): Validate JSON Documents

MariaDB JSON_VALID(value) returns 1 for valid JSON, 0 for invalid JSON, and NULL if the SQL argument is NULL. See the official MariaDB JSON_VALID reference.

JSON_VALID() checks JSON syntax only. To validate an object’s structure and field constraints, see JSON_SCHEMA_VALID(), available from MariaDB 11.1.

For the MariaDB 12.3 IS JSON predicate, which adds top-level type and unique-key options, see its operator reference.

MariaDB JSON_VALID() Syntax

Here is the syntax for the MariaDB JSON_VALID() function:

JSON_VALID(str)

Parameters

str

Required. Content that needs to be verified.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_VALID'.

Return value

The MariaDB JSON_VALID() function verifies that the given parameter is a valid JSON document. If the given argument is a valid JSON document, the JSON_VALID() function returns 1, if not a JSON document, the JSON_VALID() function returns 0.

If the argument is NULL, the JSON_VALID() function will return NULL.

Since MariaDB 10.4.3, the JSON data type alias automatically adds a JSON_VALID() check constraint. Older versions do not add this constraint automatically. See the MariaDB JSON data type reference.

MariaDB JSON_VALID() Examples

Here are some common examples to show the usages of the Mariadb JSON_VALID() function.

Numbers

SELECT JSON_VALID(1), JSON_VALID('1');

Output:

+---------------+-----------------+
| JSON_VALID(1) | JSON_VALID('1') |
+---------------+-----------------+
|             1 |               1 |
+---------------+-----------------+

Boolean values

In MariaDB, the SQL keyword TRUE is a synonym for the number 1. To test the JSON Boolean literal, pass 'true' as a string:

SELECT
    JSON_VALID(TRUE) AS sql_true_number,
    JSON_VALID('true') AS json_true_literal;

Output:

+-----------------+-------------------+
| sql_true_number | json_true_literal |
+-----------------+-------------------+
|               1 |                 1 |
+-----------------+-------------------+

Both results are valid JSON, but the first argument is the SQL integer 1; the second is the JSON Boolean value true. See MariaDB’s Boolean Literals reference.

Strings

SELECT JSON_VALID('abc'), JSON_VALID('"abc"');

Output:

+-------------------+---------------------+
| JSON_VALID('abc') | JSON_VALID('"abc"') |
+-------------------+---------------------+
|                 0 |                   1 |
+-------------------+---------------------+

Arrays

SELECT JSON_VALID('[1,2,3]'), JSON_VALID('[1,2,a]');

Output:

+-----------------------+-----------------------+
| JSON_VALID('[1,2,3]') | JSON_VALID('[1,2,a]') |
+-----------------------+-----------------------+
|                     1 |                     0 |
+-----------------------+-----------------------+

Objects

SELECT JSON_VALID('{"a": 1}'), JSON_VALID('{a: 1}');

Output:

+------------------------+----------------------+
| JSON_VALID('{"a": 1}') | JSON_VALID('{a: 1}') |
+------------------------+----------------------+
|                      1 |                    0 |
+------------------------+----------------------+

NULL parameter

The MariaDB JSON_VALID() function will return NULL if the argument is NULL.

SELECT JSON_VALID(NULL);

Output:

+------------------+
| JSON_VALID(NULL) |
+------------------+
|             NULL |
+------------------+