SQLite json_type() Function

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)Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

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"}');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('{"name": "Joe"}')
----------------------------
objectCode language: SQL (Structured Query Language) (sql)

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

SELECT json_type('[1,2,3]');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('[1,2,3]')
--------------------
arrayCode language: SQL (Structured Query Language) (sql)

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]');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('[1,2,3]', '$[1]')
----------------------------
integerCode language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('{"name": "Joe"}','$.name')
-------------------------------------
textCode language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('{"name": "Joe", "active": true }','$.active')
--------------------------------------------------------
trueCode language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('{"name": "Joe"}','$.age')
------------------------------------
nullCode language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('{"name": "Joe", "age": 25}','$.age')
-----------------------------------------------
integerCode language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Output:

json_type('{"name": "Joe", "age": 25, "weight": 176.37}','$.
------------------------------------------------------------
realCode language: SQL (Structured Query Language) (sql)

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.