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:

The following example uses the json_object() function to create a JSON object including the first name, last name, and phone of each customer:

SELECT
  json_object (
    'first_name',
    FirstName,
    'last_name',
    LastName,
    'phone',
    Phone
  ) customer
FROM
  customers
ORDER BY
  FirstName;Code language: SQL (Structured Query Language) (sql)

Output:

customer
---------------------------------------------------------------------------
{"first_name":"Aaron","last_name":"Mitchell","phone":"+1 (204) 452-6452"}
{"first_name":"Alexandre","last_name":"Rocha","phone":"+55 (11) 3055-3278"}
{"first_name":"Astrid","last_name":"Gruber","phone":"+43 015134505"}Code language: SQL (Structured Query Language) (sql)

Summary

• Use the json_object() function to return a JSON object.