SQLite JSON_TYPE function

One of the unique features of SQLite is its support for JSON data, which can be stored as a string value in a database column.

SQLite provides a range of built-in functions that allow users to extract information from JSON data. One such function is the json_type() function, which is used to determine the data type of a JSON value.

Syntax

The syntax of the json_type() function is as follows:

json_type(json_value)

Here, json_value is the JSON value whose data type needs to be determined. The json_type() function returns a string that specifies the data type of the JSON value.

The possible return values of the json_type() function are as follows:

null: If the JSON value is null.
boolean: If the JSON value is a boolean value (true or false).
integer: If the JSON value is an integer.
real: If the JSON value is a floating-point number.
text: If the JSON value is a string.
array: If the JSON value is an array.
object: If the JSON value is an object.

Examples

Let’s look at some examples of using the json_type() function in SQLite:

SELECT json_type('null'); 
Returns "null"

SELECT json_type('true'); 
Returns "boolean"

SELECT json_type('123'); 
Returns "integer"

SELECT json_type('3.14'); 
Returns "real"

SELECT json_type('"hello"'); 
Returns "text"

SELECT json_type('["apple", "banana", "orange"]'); 
Returns "array"

SELECT json_type('{"name": "John", "age": 30}'); 
Returns "object"

In the examples above, we pass different JSON values to the json_type() function and get the corresponding data type as the result.

In conclusion, the json_type() function in SQLite is a powerful tool that allows users to determine the data type of JSON values stored in a database. By using this function, users can manipulate and extract information from JSON data with ease.