The SQLite json_group_array() function is an aggregate function that returns a JSON array comprised of all values in the aggregation.

In other words, it constructs an array from the values provided by its argument.

Syntax

json_group_array(X)

Where X represents the element/s that the resulting array will consist of.

Example

Here’s a simple example to demonstrate:

SELECT json_group_array( "Fritz" );

Result:

["Fritz"]

We can see that an array was returned, with the value we provided.

However, the true power of this function is demonstrated in scenarios that use aggregate data (like in the database examples below). After all, we could have just used the json_array() function for the above example:

SELECT json_array( "Fritz" );

Result:

["Fritz"]

As mentioned, the true power of this function is in dealing with aggregate data. Therefore, json_group_array() would typically be used when querying a database.

Database Example

Suppose we have the following table:

SELECT * FROM Pets;

Result:

+-------+---------+--------+
| PetId | PetName | TypeId |
+-------+---------+--------+
| 1     | Homer   | 3      |
| 2     | Yelp    | 1      |
| 3     | Fluff   | 2      |
| 4     | Brush   | 4      |
+-------+---------+--------+

We can see that there are three columns, and we can see their names.

We can use the json_group_array() like this:

SELECT json_group_array(TypeId)
FROM Pets;

Result:

[3,1,2,4]

Here, I created an array that consists of all the TypeId values.

We can go a step further and pass another JSON function as an argument to json_group_array().

For example, if we wanted to produce a JSON object for each row in the database, we could use the json_object() function on the table like this:

SELECT json_object( 
    'PetId', PetId, 
    'PetName', PetName,
    'TypeId', TypeId 
    )
FROM Pets;

Result:

{"PetId":1,"PetName":"Homer","TypeId":3}
{"PetId":2,"PetName":"Yelp","TypeId":1}
{"PetId":3,"PetName":"Fluff","TypeId":2}
{"PetId":4,"PetName":"Brush","TypeId":4}

We can then use the json_group_array() function to provide an outer array:

SELECT json_group_array( 
        json_object(
        'PetId', PetId, 
        'PetName', PetName,
        'TypeId', TypeId 
        )
    )
FROM Pets;

Result:

[{"PetId":1,"PetName":"Homer","TypeId":3},{"PetId":2,"PetName":"Yelp","TypeId":1},{"PetId":3,"PetName":"Fluff","TypeId":2},{"PetId":4,"PetName":"Brush","TypeId":4}]

Now, all of the JSON documents that were produced by json_object() are enclosed in an array that was produced by json_group_array().