SQLite JSON_VALID()

Posted on by Ian

In SQLite, the json_valid() function checks whether or not its argument is well-formed JSON.

It returns 1 if the argument is well-formed JSON, and 0 if it’s not.

Syntax

Prior to SQLite 3.45.0 the syntax goes like this:

json_valid(X)

And from SQLite 3.45.0 the syntax goes like this:

json_valid(X,Y)

Where X is the value that gets checked and Y is an optional integer bitmask that defines what is meant by “well-formed”.

Example

Here’s an example to demonstrate the single argument syntax:

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

Result:

1

In this case the argument is valid JSON, and so 1 was returned.

Here’s another example, this time with invalid JSON:

SELECT json_valid( 'mraz' );

Result:

0

In this case the argument is not well-formed JSON and so it returned 0.

Optional Second Argument

As mentioned, from SQLite 3.45.0 we can optionally provide a second argument to specify what is meant by “well-formed”. Here’s an example of including that:

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

Result:

1

The JSON is still valid.

In this case I provided 1 as the second argument, which means that the first argument is RFC-8259 JSON text. 1 is also the default value, so if we omit the second argument, the function checks that the argument is 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 “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.

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