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)