SQLite json_object() Function

Summary: in this tutorial, you will learn how to use the SQLite json_object() function to create a JSON object.

Introduction to SQLite json_object() function

In SQLite, the json_object() function accepts zero or more pairs of name/value arguments and converts them into properties of a JSON object. The first and second arguments in each pair are property name and value, respectively.

Here’s the syntax of the json_object() function:

json_object(name1, value1, name2, value2, ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

name1, value1, … are pairs of values that correspond to property names and values.

The json_object() function returns a well-formed JSON object. If any value has a type of BLOB, the function raises an error.

SQLite json_object() function examples

Let’s create JSON objects using the json_object() function.

1) Basic json_object() function examples

The following example uses the json_object() function to create an empty object:

SELECT json_object();

Output:

json_object()
-------------
{}Code language: CSS (css)

The following example uses the json_object() function to create a flat JSON object:

SELECT
  json_object ('name', 'Bob', 'age', 25) person;Code language: SQL (Structured Query Language) (sql)

Output:

person
-----------------------
{"name":"Bob","age":25}Code language: SQL (Structured Query Language) (sql)

2) Creating a nested JSON object

The following example uses the json_object() function to create a nested JSON object:

SELECT
  json_object (
    'name',
    'Bob',
    'age',
    25,
    'favorite_colors',
    json_array ('blue', 'brown')
  ) person;Code language: SQL (Structured Query Language) (sql)

Output:

person
----------------------------------------------------------
{"name":"Bob","age":25,"favorite_colors":["blue","brown"]}Code language: SQL (Structured Query Language) (sql)

3) Using json_object() function with table data

We’ll use the customers table from the sample database: