SQLite json_array() Function

Summary: in this tutorial, you will learn how to use the SQLite json_array() function to create a JSON array from one or more values.

Introduction to the SQLite json_array() function

In SQLite, the json_array() function allows you to return a JSON array from one or more values.

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

json_array(value1, value2, ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• value1, value2, … are the values you want to convert into elements of the result JSON array.

The json_array() function returns a JSON array containing the arguments as its elements. If an argument has a type of TEXT, the json_array() converts into a JSON string.

If you don’t provide any arguments, the json_array() returns an empty JSON array.

If an argument is a BLOB, the function throws an error.

SQLite json_array() function examples

Let’s take some examples of using the json_array() function.

1) Creating a JSON array of numbers

The following example uses the json_array() function to create a JSON array that consists of numbers:

SELECT json_array(1,2,3);Code language: SQL (Structured Query Language) (sql)

Output:

json_array(1,2,3)
-----------------
[1,2,3]Code language: SQL (Structured Query Language) (sql)

2) Creating a JSON array of strings

The following example uses the json_array() function to create a JSON array of strings:

SELECT json_array('hi', 'hello', 'hallo') greeting;Code language: SQL (Structured Query Language) (sql)

Output:

greeting
----------------------
["hi","hello","hallo"]Code language: SQL (Structured Query Language) (sql)

3) Creating a JSON array of mixed values

The following example uses the jsonb_array() function to create a JSON array of values of various types:

SELECT
  json_array (
    NULL,
    'hello',
    1,
    json_array ('bye', 'good bye'),
    json_object ('name', 'bob')
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
--------------------------------------------------
[null,"hello",1,["bye","good bye"],{"name":"bob"}]Code language: SQL (Structured Query Language) (sql)

4) using the json_array() function with table data

First, create a table called quarters:

CREATE TABLE quarters(
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    months JSON NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the quarters table:

INSERT INTO quarters(name, months)
VALUES ('Q1', json_array('Jan','Feb','Mar')),
       ('Q2', json_array('Apr','May','Jun')),
       ('Q3', json_array('Jul','Aug','Sep')),
       ('Q4', json_array('Oct','Nov','Dec'));Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the quarters table:

SELECT * FROM quarters;Code language: SQL (Structured Query Language) (sql)

Output:

id  name  months
--  ----  -------------------
1   Q1    ["Jan","Feb","Mar"]
2   Q2    ["Apr","May","Jun"]
3   Q3    ["Jul","Aug","Sep"]
4   Q4    ["Oct","Nov","Dec"]Code language: SQL (Structured Query Language) (sql)

Summary

• Use the json_array() function to create a JSON array from one or more values.