SQLite 3 reference · JSON Functions

SQLite JSON_GROUP_ARRAY() Function

Aggregate the values of a group into one JSON array, one element per row. SQL NULLs become JSON null.

SQLite 3 Since 3.10.0

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

json_group_array(X)

Parameter	Description
`X`	The value to collect.

JSON array text.

One array per groupTry it yourself

SELECT country, json_group_array(name) AS names
FROM customers
GROUP BY country
HAVING count(*) > 1;

Output

country | names
UK | ["Ada Lovelace","Alan Turing"]
USA | ["Grace Hopper","Linus Torvalds","Margaret Hamilton","Barbara Liskov","Katherine Johnson"]

An array of objectsTry it yourself

SELECT json_group_array(json_object('id', order_id, 'status', status)) AS orders
FROM orders
WHERE customer_id = 1;

Output

orders
[{"id":1,"status":"delivered"},{"id":3,"status":"delivered"},{"id":9,"status":"pending"}]

Version	Change
SQLite 3.10.0	Added

JSON_GROUP_OBJECT() · Aggregate key/value pairs from the rows of a group into one JSON object, such as a name-to-price lookup.
GROUP_CONCAT() · Join a group's non-NULL values into one string, separated by commas or a separator you give.
JSON_ARRAY() · Build a JSON array from the arguments. SQL NULL becomes JSON null; numbers stay numbers.
SQL String Functions · tutorial