SQLite json_group_array() Function

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:

SQLite json_group_array() function - sample tables

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.

Was this tutorial helpful ?