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 objectThe 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 1The 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 textArray 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 integerjson_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.