SQLite json_valid() Function

SQLite json_valid() tests whether an input is well-formed JSON. It returns 1 for valid input, 0 for invalid input, and SQL NULL if an argument is SQL NULL. The optional second argument controls whether SQLite accepts strict JSON text, JSON5 syntax, or binary JSONB.

Syntax

json_valid(value)
json_valid(value, flags)

• value is the text or binary value to validate.
• flags is an optional integer bitmask, supported in SQLite 3.45.0 and later. When omitted, SQLite uses 1, meaning standard RFC-8259 JSON text.

The most useful flag values are:

The 4 bit is a fast superficial JSONB check; 8 performs a deeper JSONB validation. Flags can be combined, such as 2 + 4 = 6. This function reports whether input matches the selected format; it does not repair malformed JSON.

Examples

Validate ordinary JSON text

SELECT
    json_valid('{"name":"Ada"}') AS valid_object,
    json_valid('{"name":}') AS invalid_object,
    json_valid('"Ada"') AS valid_string,
    json_valid(NULL) AS null_input;

valid_object  invalid_object  valid_string  null_input
------------  --------------  ------------  ----------
1             0               1             NULL

JSON documents can be scalars (1, true, or a quoted string), arrays, or objects. SQL NULL produces SQL NULL, not zero.

JSON5 versus strict JSON

JSON5 allows features such as unquoted object keys. The one-argument function remains strict for backwards compatibility.

SELECT
    json_valid('{name:"Ada"}') AS strict_json,
    json_valid('{name:"Ada"}', 2) AS json5,
    json_valid('{name:"Ada"}', 6) AS json5_or_jsonb;

strict_json  json5  json5_or_jsonb
-----------  -----  --------------
0            1      1

This example requires SQLite 3.45.0 or later. Use json_valid(value, 1) when your input must conform to standard JSON rather than JSON5.

Validate SQLite JSONB

SELECT
    json_valid(jsonb('{"id":1}')) AS default_text_check,
    json_valid(jsonb('{"id":1}'), 6) AS json5_or_jsonb,
    json_valid(jsonb('{"id":1}'), 8) AS strict_jsonb;

default_text_check  json5_or_jsonb  strict_jsonb
------------------  --------------  ------------
0                   1               1

jsonb() stores SQLite’s binary JSON representation as a BLOB. The default json_valid(value) tests only JSON text. The JSONB functions and the two-argument json_valid() arrived in SQLite 3.45.0.

Filter invalid JSON before extraction

WITH documents(payload) AS (
    VALUES ('{"id":1}'), ('broken'), ('{"id":2}')
)
SELECT json_extract(payload, '$.id') AS id
FROM documents
WHERE json_valid(payload) = 1
ORDER BY id;

id
--
1
2

This pattern avoids attempting to extract fields from malformed text. For mixed JSON5/JSONB inputs on SQLite 3.45.0 or later, consider json_valid(payload, 6) instead.

Frequently asked questions

Why does json_valid() reject JSON5?

The default mode intentionally accepts only standard JSON. Pass 2 for JSON5 or 6 for JSON5/JSONB on SQLite 3.45.0 or later.

Is JSON null the same as SQL NULL?

No. json_valid('null') returns 1 because 'null' is valid JSON text. json_valid(NULL) returns SQL NULL because the input itself is missing.

Why do I get “wrong number of arguments”?

Your SQLite build may be older than 3.45.0. Check SELECT sqlite_version();. The SQLiz SQLite Playground currently embeds SQLite 3.49.1 and supports the examples above.

See also json_type() and json_extract(), or consult the official SQLite JSON documentation.