Home » SQLite JSON Functions » SQLite json_type() Function

SQLite json_type() Function

Savigo
You organize your data. Now organize your savings.
Set savings goals, track your progress, and stay on course.
Get Savigo for iPhone 

Summary: in this tutorial, you will learn how to use the SQLite json_type() function to return the type of a JSON element.

Introduction to the SQLite json_type() function #

In SQLite, the json_type() function returns the type of a JSON element. Here’s the syntax of the json_type() function:

json_type(x)

In this syntax:

  • x is the JSON element of which you want to get the type.

If you want to get the JSON type of an element specified by a path, you can use the following syntax:

json_type(x, path)

In this syntax, the json_type() function returns the type of the element in x selected by the path.

The json_type() function returns the following SQL text values:

  • null
  • false
  • true
  • integer
  • real
  • text
  • array
  • object

If the path does not exist in x, then the json_type() function returns NULL.

SQLite json_type() function examples #

The following example uses the json_type() function to return the json type of a JSON object:

SELECT json_type('{"name": "Joe"}');

Output:

json_type('{"name": "Joe"}')
----------------------------
object

The following example uses the json_type() function to return the json type of a JSON array:

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

Output:

json_type('[1,2,3]')
--------------------
array

The following example uses the json_type() function to return the json type of the first element in a JSON array:

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

Output:

json_type('[1,2,3]', '$[1]')
----------------------------
integer

The following example uses the json_type() function to return the json type of the value of the name property in a JSON object:

SELECT json_type('{"name": "Joe"}','$.name');

Output:

json_type('{"name": "Joe"}','$.name')
-------------------------------------
text

The following example uses the json_type() function to return the json type of the value of the active property in a JSON object:

SELECT json_type('{"name": "Joe", "active": true }','$.active');

Output:

json_type('{"name": "Joe", "active": true }','$.active')
--------------------------------------------------------
true

The following example uses the json_type() function to return the json type of the value of a property that does not exist:

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

Output:

json_type('{"name": "Joe"}','$.age')
------------------------------------
null

The following statement uses the json_type() function to return the json type of the value of the age property:

SELECT json_type('{"name": "Joe", "age": 25}','$.age');

Output:

json_type('{"name": "Joe", "age": 25}','$.age')
-----------------------------------------------
integer

The following statement uses the json_type() function to return the json type of the value of the weight property:

SELECT json_type('{"name": "Joe", "age": 25, "weight": 176.37}','$.weight');

Output:

json_type('{"name": "Joe", "age": 25, "weight": 176.37}','$.
------------------------------------------------------------
real

Summary #

  • Use the json_type(x) function to get the JSON type of the outermost element of x.
  • Use the json_type(x, path) function to obtain the JSON type of the element in x specified by the path.

Was this tutorial helpful?