JSON_GROUP_OBJECT

An aggregate function that returns a JSON object composed of key-value pairs from two specified columns within a group.

This function operates on a set of rows (defined by a GROUP BY clause, or all rows if not specified) and constructs a single JSON object. For each row in the group, the value from the key column is used as a key, and the value from the value column is used as its corresponding value in the object.

The key column must contain text values. If a key is NULL or a key is repeated, the behavior might be unpredictable, and typically the last value for a repeated key is used. Values from the value column are converted to their appropriate JSON representation (string, number, boolean, or null).

Example:

// Assuming 'Settings' table has 'keyName' and 'keyValue' columns.
val keyCol = textColumn("keyName")
val valueCol = textColumn("keyValue")

// Aggregate all settings into a single JSON object.
// SELECT JSON_GROUP_OBJECT(keyName, keyValue) FROM Settings;
// Result might be: '{"theme":"dark","fontSize":"14"}'
select(JSON_GROUP_OBJECT(keyCol, valueCol)).from(Settings).execute()

// Group settings by user and aggregate them
val userIdCol = intColumn("userId")
// SELECT userId, JSON_GROUP_OBJECT(keyName, keyValue) FROM Settings GROUP BY userId;
select(userIdCol, JSON_GROUP_OBJECT(keyCol, valueCol)).from(Settings).groupBy(userIdCol).execute()

Parameters

See also