SQLite json_group_object() Function

The SQLite json_group_object() function is an aggregate function that returns a JSON object containing the key-value pairs of the specified columns in a group.

Similarly, the json_group_array() function returns an JSON array containing all values of a specified column in a group.

Syntax

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

json_group_object(key_expr, value_expr)

Parameters

key_expr

Required. A column name or expression whose values is the keys in JSON.

value_expr

Required. A column name or expression whose values is the values ​​in JSON.

Return value

The SQLite json_group_object() function returns an object that contains key-value pairs consisting of the specified columns in a group.

Examples

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

Basic usage

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

SELECT json_group_object('x', 'y');

json_group_object('x', 'y')
---------------------------
{"x":"y"}

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

Use json_group_object() Do Aggregate Queries

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

SELECT 'Tim' name, 'Math' subject, 'A' grade
UNION
SELECT 'Tim' name, 'English' subject, 'B' grade
UNION
SELECT 'Tom' name, 'Math' subject, 'B' grade
UNION
SELECT 'Tom' name, 'English' subject, 'A' grade;

name  subject  grade
----  -------  -----
Tim   English  B
Tim   Math     A
Tom   English  A
Tom   Math     B

Here, we have some rows for user grades. where, in each row is the user’s grade for one subject.

If we need to put everyone’s grade information into one line and everyone’s grades in one object (the key-value pairs are from the value of subject and the value of grade), use the following statement:

SELECT
    t.name,
    json_group_object(t.subject, t.grade) grades
FROM (
    SELECT 'Tim' name, 'Math' subject, 'A' 'grade'
    UNION
    SELECT 'Tim' name, 'English' subject, 'B' 'grade'
    UNION
    SELECT 'Tom' name, 'Math' subject, 'B' 'grade'
    UNION
    SELECT 'Tom' name, 'English' subject, 'A' 'grade'
) t
GROUP BY t.name;

name  grades
----  --------------------------
Tim   {"English":"B","Math":"A"}
Tom   {"English":"A","Math":"B"}

Here, we use the GROUP BY clause and json_group_object() function to put the subjects and grades having the same name into a JSON object of the column grades.

The json_group_object(t.subject, t.grade) function uses the value of the subject column as the key and the value of the grade column as the value.