Summary: in this tutorial, you will learn how to use the SQLite json_group_array()
function to aggregate values into a JSON array.
Introduction to the SQLite json_group_array() function
In SQLite, the json_group_array()
function allows you to aggregate values into a JSON array.
Here’s the syntax of the json_group_array()
function:
json_group_array(x)
Code language: SQL (Structured Query Language) (sql)
In this syntax:
x
: is a column that contains values you want to aggregate into a JSON array.
The json_group_array()
function returns a JSON array that contains values from the x column.
SQLite json_group_array() function example
We’ll use the albums and tracks table from the sample database:
The following example uses the json_group_array()
function to aggregate track names of albums into a JSON array:
SELECT
Title,
json_group_array (Name) tracks
FROM
tracks
INNER JOIN albums USING (AlbumId)
GROUP BY
Title
ORDER BY
Title desc;
Code language: SQL (Structured Query Language) (sql)
Output:
Title tracks
--------------------------- ------------------------------------------------------------
[1997] Black Light Syndrome ["The Sun Road","Dark Corners","Duende","Black Light Syndrom
e","Falling in Circles","Book of Hours","Chaos-Control"]
Zooropa ["Zooropa","Babyface","Numb","Lemon","Stay (Faraway, So Clos
e!)","Daddy's Gonna Pay For Your Crashed Car","Some Days Are
Better Than Others","The First Time","Dirty Day","The Wande
rer"]
Code language: SQL (Structured Query Language) (sql)
How it works.
First, join the albums table with the tracks
table by matching the values in the AlbumId
column in both tables.
Second, group the tracks by album titles using the GROUP BY
clause.
Third, aggregate track names in each album into a JSON array using the json_group_array()
function.
Summary
- Use the
json_group_array()
function to aggregate values into a JSON array.