Validating Text as JSON With json_valid in SQLite Tables

SQLite will store any text in a TEXT column, so the first line of defense for a JSON column is a validity check. json_valid is the function for it, and the behavior of that function is more subtle than the name suggests: it has flags, it has a limit on depth, it can say "valid" for text a strict parser would reject, and used in a CHECK constraint it has a loophole for NULL. This article lists exactly what passes and what fails with each flag, shows how to find and fix bad rows, how to add the check to a table that already exists and how to validate JSONB blobs.

What counts as valid

json_valid(x) returns 1 if x is a well-formed JSON text, 0 if not, and NULL for a NULL argument. The strict reading is the default, and a second argument selects other readings:

The flags are bits and can be added: json_valid(x, 3) accepts either kind of text. I ran 33 inputs through the strict and JSON5 readings. The interesting ones:

input                          strict  JSON5   error position
{"a":1}                        1       1       0
{"a":1,}  trailing comma       0       1       0
{'a':1}   single quotes        0       1       0
{a:1}     unquoted key         0       1       0
{"a":1} // c   line comment    0       1       0
{"a":0x1F}  hex number         0       1       0
{"a":NaN}                      0       1       0
{"a":01}  leading zero         0       0       7
{"a":undefined}                0       0       6
raw newline inside a string    0       1       0
BOM before the document        0       1       0
{"a":1}{"b":2}  two documents  0       0       1
{"a":1} x  trailing text       0       0       1
(empty string)                 0       0       1
null, 1, "x" as documents      1       1       0
{"a":1,"a":2}  duplicate keys  1       1       0
[ x100, ] x100  100 deep       1       1       0
[ x2000, ] x2000  2000 deep    0       0       1001

A few points. Duplicate keys are valid; SQLite does not check uniqueness, and the functions that read them pick one. Scalars are valid documents: null, 1 and "x" pass, so json_valid does not mean "is an object". Depth is limited: 100 nested arrays passed, 2,000 failed with the position at 1001, which is SQLite's depth limit of 1000. The strict reading is strict: a raw newline or tab inside a string, a byte order mark, hexadecimal numbers and NaN all fail, and they are all things people paste in from JavaScript tools and log lines.

The position function

json_error_position(x) returns 0 for text that is valid and the one-based position of the first problem otherwise. It measures against JSON5, so a document that is invalid strict JSON but valid JSON5 returns 0, as the trailing-comma case does. When json_valid says 0 and json_error_position says 0, the document is JSON5, not JSON, and a strict consumer elsewhere will reject it. For genuinely broken text, the position helps: {bad gave 5, {"a":tru} gave 7, and an empty string gave 1.

Putting it in a CHECK

create table events (id integer primary key, payload text check (json_valid(payload)));

Remember how CHECK works in SQL: a row is rejected only if the expression is false. If it evaluates to NULL, the row passes. That has two consequences for JSON.

**A NULL value passes json_valid.** json_valid(NULL) is NULL, so a nullable column with check (json_valid(v)) accepted NULL. That is often what you want (no value yet). When it is not, add not null.

A missing key passes a type test. I made a stricter table:

create table typed (
  id  integer primary key,
  doc text not null check (
    json_valid(doc) and json_type(doc) = 'object'
    and json_type(doc, '$.qty') = 'integer' and json_type(doc, '$.sku') = 'text')
);

and tried five documents. {"sku":"a","qty":1} went in. {"sku":"a","qty":"1"} and {"sku":"a","qty":1.5} were rejected, since the type of qty was text and real. And {"sku":"a"}, with no qty at all, was accepted: json_type(doc, '$.qty') is NULL for a missing path, NULL = 'integer' is NULL, and a NULL check passes. To require the key, test it explicitly: json_type(doc, '$.qty') is 'integer'. The IS operator treats NULL as a value and gives false when the type is NULL, so a missing key fails. Use is rather than = for every presence-plus-type check.

Extra keys, as in {"sku":"a","qty":1,"extra":true}, pass; there is no way to forbid unknown keys short of comparing the key list with json_each, which is not allowed in a CHECK (subqueries are not permitted there). For tight schemas, validate in the application or a trigger.

Finding bad rows

Before you enforce anything on a table with data, look at what is there:

select id, payload from events where payload is not null and not json_valid(payload);

On a six-row test table the rows were {bad, an empty string and {"a":1,} (the trailing comma). The payload is not null is needed: not json_valid(NULL) is NULL, so the NULL row would be excluded anyway, but if you also want to list NULLs, json_valid(payload) is not 1 lists them along with the bad ones (ids 2, 3, 4 and 5 in my test). To see why each is bad, add json_error_position(payload). For a large table with a few bad rows, this query is the diagnosis; for one with many, group by the first twenty characters to see the common shapes.

Adding the check to a table that exists

SQLite is limited here. ALTER TABLE ... ADD COLUMN accepts a new column with a CHECK and tests it against the default (alter table events add column meta text check (json_valid(meta)) worked). There is no ADD CONSTRAINT for an existing column. The way is to rebuild the table:

create table events_new (id integer primary key, payload text check (json_valid(payload)));
insert into events_new select id, payload from events where json_valid(payload);
-- review the rejected rows from events, then:
drop table events;
alter table events_new rename to events;

I inserted only the valid rows (2 of 6 in the test), and an attempt to insert the others failed with CHECK constraint failed: json_valid(payload), which is the point. Do the swap in a transaction, recreate indexes and triggers, and decide what to do with the rejects (repair, set to NULL, move to a quarantine table) before you drop the old table.

To audit a table that may already contain violations, for example after a bulk load with checks off, pragma integrity_check and pragma quick_check report them. After I inserted bad with pragma ignore_check_constraints = on, both printed CHECK constraint failed in chk. That setting exists for bulk loading and is dangerous to leave on; turn it off in the same script.

JSONB blobs

For a column of JSONB, the check is json_valid(doc, 8). In the test it accepted jsonb('{"a":1}') and rejected both the text '{"a":1}' and the garbage blob x'ff'. With the default flag, a JSONB blob is not valid (json_valid(jsonb('{"a":1}')) returned 0), so do not use a plain json_valid(col) on a binary column. For a column where either form is acceptable, flag 9 (8 + 1) allows blobs and strict text. The format is described in the JSONB format added in SQLite 3.45.

Lenient on purpose

You can decide to accept JSON5: check (json_valid(doc, 2)) allowed {a:1,}, and json(doc) returned the strict form {"a":1}. The JSON functions already read JSON5, so a column that passes flag 2 behaves in queries. Do it only when every consumer is SQLite, or when you convert on read with json(). A strict consumer elsewhere (a browser, another database) will reject the stored text. For public data and exports, hold the strict line, and convert on write: insert ... values (json(:doc)) normalizes the text and rejects what is unreadable.

Related checks

• Object-only: json_type(doc) = 'object' rejected [1,2] and scalars.

• Size: length(doc) < 100000 keeps documents bounded.

• Required keys: json_type(doc, '$.id') is 'integer', as above.

• Enumerated values: doc ->> '$.status' in ('open', 'closed'). A missing status gives NULL, and NULL in (...) is NULL, which passes; add is not null to require it.

The same pattern in other databases is described in validating JSON before it gets into a MySQL column, and the broader error picture in finding the exact line of a JSON syntax error. To check a single document by eye, the JSON formatter reports the position of the first error in the browser.

When json_valid is not enough

You need a schema. json_valid checks syntax, not shape. Required keys, types and ranges can be approximated with json_type and ->> in a CHECK, up to a point. A real JSON Schema validation belongs in the application (validating JSON with jsonschema in Python).

You need to forbid duplicate keys. They are valid JSON; SQLite reads one of them. A strict check needs a parser.

The data may arrive as JSON5 or with comments. Decide whether to normalize on the way in, rather than discovering the difference when an export breaks.

Rows were written with checks off. Run pragma integrity_check after a bulk load.

The functions are documented in the SQLite manual: json_valid and json_error_position, and CHECK semantics in the CREATE TABLE page.