SQLite json_group_array() Function

The SQLite json_group_array() function is an aggregate function that returns a JSON array containing all the values in a group.

Similarly, the json_group_object() function returns an object containing the specified columns in the group.

The json_group_array() function is similar to the group_concat() function, except that the group_concat() function return a string containing all the values.

Syntax

Here is the syntax of the SQLite json_group_array() function:

json_group_array(expr)

Parameters

expr: Required. A column name or expression.

Return value

The SQLite json_group_array() function returns an array that contains all the values in the specified expression or column in a group.

Examples

Here are some examples to show the usages of json_group_array().

Basic usage

This example illustrates the basic usage of the SQLite json_group_array() function.

SELECT json_group_array('Hello');

json_group_array('Hello')
-------------------------
["Hello"]

This usage doesn’t have much practical value. The SQLite json_group_array() is generally used in aggregate queries on tables.

Use `json_group_array()` Get a Json Array

To demonstrate the usage of json_group_array(), we simulate a table with the following UNION statement:

SELECT 'Tim' name, 'Football' hobby
UNION
SELECT 'Tim' name, 'Baseball' hobby
UNION
SELECT 'Tom' name, 'Piano' hobby
UNION
SELECT 'Tom' name, 'violin' hobby;

name  hobby
----  --------
Tim   Baseball
Tim   Football
Tom   Piano
Tom   violin

Here, we have some rows about user hobbies. Among them, each row is a hobby of the user.

If we need to put echo user’s hobbies into each line, and each user’s hobbies in an array, use the following statement:

SELECT
    t.name,
    json_group_array(t.hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby
    UNION
    SELECT 'Tim' name, 'Baseball' hobby
    UNION
    SELECT 'Tom' name, 'Piano' hobby
    UNION
    SELECT 'Tom' name, 'violin' hobby
) t
GROUP BY t.name;

name  hobbies
----  -----------------------
Tim   ["Baseball","Football"]
Tom   ["Piano","violin"]

Here, we use the GROUP BY clause and json_group_array() function to put the hobby information in the JSON array of the column hobbies having the same name.