SQLite json_valid() Function

Summary: in this tutorial, you will learn how to use the SQLite json_valid() function to check if a text string represents valid JSON data.

Introduction to the json_valid() function

The json_valid() function checks whether a string contains valid JSON.

Here’s the syntax of the json_valid() function:

json_valid(json_value)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_value is a string you want to check for a valid JSON format.

The json_valid() function returns 1 if the json_value is valid JSON, otherwise, it returns 0. The json_valid() function returns NULL if the input string is NULL.

SQLite json_valid() function examples

Let’s take some examples of using the json_valid() function.

1) Checking a valid JSON string

The following example uses the json_valid() function to check if a string is a valid JSON:

SELECT json_valid('{"name": "Bob"}') result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
1Code language: SQL (Structured Query Language) (sql)

The json_valid() function returns 1 because the json string is valid.

2) Checking an invalid JSON string

SELECT json_valid('{age: 25}') result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
0Code language: SQL (Structured Query Language) (sql)

This example returns 0 because the input json is invalid.

3) Using json_valid() function with table data

First, create a table called persons:

CREATE TABLE persons(
    id INTEGER PRIMARY KEY,
    data TEXT
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the persons table:

INSERT INTO persons(data) 
VALUES 
   ('{"name": "Alice", "age": 30}'),
   ('{"name": "Bob", "city": "New York"}'),
   ('invalid_json');Code language: SQL (Structured Query Language) (sql)

Third, validate json data using the json_valid() function:

SELECT id, data,  json_valid(data) AS is_valid
FROM persons;Code language: SQL (Structured Query Language) (sql)

Output:

id  data                                 is_valid
--  -----------------------------------  --------
1   {"name": "Alice", "age": 30}         1
2   {"name": "Bob", "city": "New York"}  1
3   invalid_json                         0Code language: SQL (Structured Query Language) (sql)

Defining well-formed JSON

Starting from the SQLite version 3.45.0, the json_valid() function has a second optional argument that defines what it means by “well-formed”.

json_valid(json_value, flags );Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_value is a string that you want to check for valid JSON.
• flags is an integer bitmask that specifies what constitutes “well-form” JSON.

SQLite defines the following bits of the flags:

• 0x01 – Check the json_value against the canonical RFC-8259 JSON, without any extensions.
• 0x02 – Check the json_value against the JSON5 extensions.
• 0x04 – If json_value is a BLOB that superficially appears to be SQLite’s internal binary  representation of JSON (JSONB)
• 0x08 – If json_value is a BLOB that strictly appears to be SQLite’s internal binary representation of JSON (JSONB)

By combining the above bits, you can form some useful flags. For example, flags 6 checks if the json_value is JSON5 text or JSONB.

The following example uses the json_valid() function to check if a JSON value is JSON5 text or JSONB:

SELECT json_valid('{"name": "Joe"}', 6) valid;Code language: SQL (Structured Query Language) (sql)

Output:

valid
-----
1Code language: SQL (Structured Query Language) (sql)

Summary

• Use the json_valid() function to check whether a text string represents valid JSON data.