Menu

SQLite json_type() Function

SQLite json_type() reports the JSON type of a document or of the value at a specific JSON path. It is particularly useful when you need to distinguish an explicit JSON null from a key that is not present at all.

Syntax

json_type(json_document)
json_type(json_document, path)

With no path, SQLite reports the type of the document’s top-level value. With a path such as $.user.age or $.items[0], it reports the type of that selected value.

The result is one of these SQL text values:

JSON value Result
Object 'object'
Array 'array'
String 'text'
Whole number 'integer'
Floating-point number 'real'
Boolean true 'true'
Boolean false 'false'
JSON null 'null'
Missing path SQL NULL

Malformed JSON or an invalid path raises an error. SQL NULL input yields SQL NULL.

Examples

Inspect types inside an object

SELECT
    json_type('{"name":"Ada","age":37}', '$.name') AS name_type,
    json_type('{"name":"Ada","age":37}', '$.age') AS age_type,
    json_type('{"name":"Ada","age":37}', '$') AS root_type;
name_type  age_type  root_type
---------  --------  ---------
text       integer   object

The function returns the JSON type, not the SQL storage class of the entire document.

Distinguish JSON null from a missing key

SELECT
    json_type('{"note":null}', '$.note') AS explicit_null,
    json_type('{"note":null}', '$.missing') AS missing_key,
    json_extract('{"note":null}', '$.note') IS NULL AS extracted_is_null;
explicit_null  missing_key  extracted_is_null
-------------  -----------  -----------------
null           NULL         1

The first null is a SQL text value, identifying a JSON null. The missing key produces actual SQL NULL. A single-path json_extract() returns SQL NULL for both cases, so use json_type() when their distinction matters.

Check an array element

SELECT
    json_type('[10, true, "ok"]', '$[0]') AS first_type,
    json_type('[10, true, "ok"]', '$[1]') AS second_type,
    json_type('[10, true, "ok"]', '$[2]') AS third_type;
first_type  second_type  third_type
----------  -----------  ----------
integer     true         text

Array indices start at zero. A path that points beyond the end of an array returns SQL NULL.

json_type() versus typeof()

SELECT
    json_type('{"active":true}', '$.active') AS json_value_type,
    typeof(json_extract('{"active":true}', '$.active')) AS sql_value_type;
json_value_type  sql_value_type
---------------  --------------
true             integer

json_type() tells you what the original JSON value represents; SQL typeof() tells you the SQLite storage class of a SQL expression. Boolean JSON values extracted as scalars become SQL integers (1 or 0).

Frequently asked questions

Why is json_type() returning NULL?

The path may not exist, or one of the SQL arguments may be NULL. A JSON member that explicitly contains JSON null returns the text 'null' instead.

How can I avoid errors on invalid JSON text?

Validate external JSON text first with json_valid(), then call json_type() or json_extract() only for valid rows.

Try it online

You can run all the examples in the SQLite Playground. For more details and JSON path syntax, consult SQLite JSON documentation.