JSON_VALID()¶

JSON_VALID() tests whether a string is a syntactically valid JSON value. Returns 1 for valid JSON, 0 for invalid JSON, and NULL if the argument is NULL.

Description¶

JSON_VALID() parses the input string and checks whether it conforms to the JSON grammar. Any valid JSON value (object, array, string, number, boolean, or null) returns 1. Malformed JSON strings, non-JSON strings, and empty strings return 0.

The function can be used in WHERE clauses to filter rows containing valid JSON, or in HAVING with GROUP BY to count valid vs invalid JSON values.

Syntax¶

> JSON_VALID(val)

Arguments¶

Arguments	Description
val	Required. The string to test for JSON validity. Can be a string literal, column reference, or result of a JSON function.

Examples¶

DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;

-- Valid JSON values
SELECT JSON_VALID('{"a": 1}');
SELECT JSON_VALID('[1, 2, 3]');
SELECT JSON_VALID('"hello"');
SELECT JSON_VALID('null');
SELECT JSON_VALID('true');
SELECT JSON_VALID('false');
SELECT JSON_VALID('42');
SELECT JSON_VALID('3.14');
SELECT JSON_VALID('{}');
SELECT JSON_VALID('[]');

-- Invalid JSON
SELECT JSON_VALID('hello');
SELECT JSON_VALID('');

-- NULL argument
SELECT JSON_VALID(NULL);

-- Nested JSON
SELECT JSON_VALID('{"a": {"b": [1,2,3]}, "c": "hello"}');
SELECT JSON_VALID('[{"a":1}, [2,3], "str"]');

-- Invalid JSON strings
SELECT JSON_VALID('{invalid');
SELECT JSON_VALID('[1,2,');
SELECT JSON_VALID('{"a":}');
SELECT JSON_VALID('{key: "no_quotes"}');

-- Via JSON functions (always valid)
SELECT JSON_VALID(JSON_OBJECT('a', 1, 'b', 2));
SELECT JSON_VALID(JSON_ARRAY(1, 2, 3));

-- Table usage with VARCHAR column
CREATE TABLE t1 (a VARCHAR(100));
INSERT INTO t1 VALUES ('{"a":1}'), ('[1,2,3]'), ('"hello"'), ('{}'), ('invalid'), (NULL), ('');
SELECT a, JSON_VALID(a) FROM t1;

-- WHERE clause filtering
SELECT * FROM t1 WHERE JSON_VALID(a);

-- GROUP BY with HAVING
SELECT JSON_VALID(a), COUNT(*) FROM t1 GROUP BY JSON_VALID(a) ORDER BY 1;

DROP TABLE t1;
DROP DATABASE dbfuncs;