SQLite’s json_valid() function allows us to check whether a JSON string is well formed or not.

Prior to SQLite 3.45.0 the json_valid() function only accepts one argument – the value to check. However, from SQLite 3.45.0 (released on 15 January 2024), we can now provide an optional second argument to define what valid – or “well formed” – means.

Example

Here’s an example of using the two-argument syntax of json_valid():

SELECT json_valid( '{ "a" : 1 }', 1 );

Result:

1

The JSON is valid, based on the second argument defining what “valid” is. In this case the value is 1, which means RFC-8259 JSON text. 1 is also the default value, so if we hadn’t used a second argument, the function would have used checked that the (sole) argument was RFC-8259 JSON text.

Now let’s change the second argument (while keeping the first argument the same):

SELECT json_valid( '{ "a" : 1 }', 8 );

Result:

0

This time the JSON is invalid. That’s because we changed the criteria for what it means to be valid, or “well formed”. The second argument of 8 means that the first argument is only valid if it’s strictly conforming JSONB. In this case it’s not and so it’s deemed to be invalid.

Defined Values for the Second Argument

According to the SQLite documentation, the following bits of the second argument are currently defined:

• 0x01 → The input is text that strictly complies with canonical RFC-8259 JSON, without any extensions.
• 0x02 → The input is text that is JSON with JSON5 extensions described above.
• 0x04 → The input is a BLOB that superficially appears to be JSONB.
• 0x08 → The input is a BLOB that strictly conforms to the internal JSONB format.

By combining bits, the following useful values of Y can be derived:

• 1 → X is RFC-8259 JSON text
• 2 → X is JSON5 text
• 4 → X is probably JSONB
• 5 → X is RFC-8259 JSON text or JSONB
• 6 → X is JSON5 text or JSONB ← This is probably the value you want
• 8 → X is strictly conforming JSONB
• 9 → X is RFC-8259 or strictly conforming JSONB
• 10 → X is JSON5 or strictly conforming JSONB