The SQLite json_type() function returns the type of the outermost element of the given JSON.

We provide the JSON as an argument when we call the function.

We can optionally pass a path, which allows us to get the type of a specific array element or object member within the JSON.

Syntax

We can use the function in the following ways:

json_type(X)
json_type(X,P)

Where X represents the JSON, and P is an optional argument that represents the path to get the type of.

The function returns one of the following SQL text values: null, true, false, integer, real, text, array, or object.

Example

Here’s an example to demonstrate how it works:

SELECT json_type('{ "name" : "Fluffy", "age" : 10 }');

Result:

object

Here I only provided the JSON – I didn’t provide a path. In this case, the outermost JSON is an object.

Here’s an array:

SELECT json_type('[ 1, 2, 3 ]');

Result:

array

We can even do stuff like this:

SELECT json_type(10.45);

Result:

real

In this case, my argument isn’t actually a JSON object or array, but json_type() was able to return its type anyway.

Specify a Path

Here’s what happens when we specify a path:

SELECT json_type('{ "name" : "Fluffy", "age" : 10 }', '$.age');

Result:

integer

In this case, I specified a path of $.age, which resulted in the type of the age member being returned.

Let’s get the type of the name member:

SELECT json_type('{ "name" : "Fluffy", "age" : 10 }', '$.name');

Result:

text

Let’s do it on an array:

SELECT json_type('[ "Wag", 2, 1.5, null, true, false ]', '$[0]');

Result:

text

That got the type of the first element (SQLite arrays are zero-based, so 0 is for the first element).

Let’s get the types of all elements in the array:

SELECT 
    json_type('[ "Wag", 2, 1.5, null, true, false  ]', '$[0]') AS "0",
    json_type('[ "Wag", 2, 1.5, null, true, false  ]', '$[1]') AS "1",
    json_type('[ "Wag", 2, 1.5, null, true, false  ]', '$[2]') AS "2",
    json_type('[ "Wag", 2, 1.5, null, true, false  ]', '$[3]') AS "3",
    json_type('[ "Wag", 2, 1.5, null, true, false  ]', '$[4]') AS "4",
    json_type('[ "Wag", 2, 1.5, null, true, false  ]', '$[5]') AS "5";

Result:

+------+---------+------+------+------+-------+
|  0   |    1    |  2   |  3   |  4   |   5   |
+------+---------+------+------+------+-------+
| text | integer | real | null | true | false |
+------+---------+------+------+------+-------+