JSON_VALID() – Test for Valid JSON in MySQL

When using MySQL, you can use the JSON_VALID() function to test whether or not a string expression contains valid JSON.

If the expression contains valid JSON, JSON_VALID() returns 1, otherwise it returns 0.

Syntax

The syntax goes like this:

JSON_VALID(val)

Where val is the value for which you’re testing for valid JSON.

Example 1 – Valid JSON

Here’s an example to demonstrate what happens when the string contains valid JSON.

SELECT JSON_VALID('{"Name": "Bart"}') AS Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Example 2 – Invalid JSON

Here’s an example to demonstrate what happens when the string doesn’t contain valid JSON.

SELECT JSON_VALID('Name: Bart') AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 3 – A Database Example

In this database query, the results are only returned where the Collections.Contents column contains valid JSON.

This particular column uses a data type of json to store the JSON document.

SELECT Contents
FROM Collections
WHERE JSON_VALID(Contents) = 1;

Result:

+------------+
| Contents   |
|------------|
| [
    {
        "ArtistName": "AC/DC",
        "Albums": [
            {
                "AlbumName": "Powerage"
            }
        ]
    },
    {
        "ArtistName": "Devin Townsend",
        "Albums": [
            {
                "AlbumName": "Ziltoid the Omniscient"
            },
            {
                "AlbumName": "Casualties of Cool"
            },
            {
                "AlbumName": "Epicloud"
            }
        ]
    },
    {
        "ArtistName": "Iron Maiden",
        "Albums": [
            {
                "AlbumName": "Powerslave"
            },
            {
                "AlbumName": "Somewhere in Time"
            },
            {
                "AlbumName": "Piece of Mind"
            },
            {
                "AlbumName": "Killers"
            },
            {
                "AlbumName": "No Prayer for the Dying"
            }
        ]
    }
]            |
+------------+

Here’s what happens if we change the statement to return the data only if it isn't valid JSON.

SELECT Contents
FROM Collections
WHERE JSON_VALID(Contents) = 0;

Result:

Empty set (0.00 sec)