Convert a Table to a JSON Document with JSON_GROUP_OBJECT() in DuckDB

by

In DuckDB, the json_group_object() function is a convenient way to aggregate data into JSON objects by pairing keys and values across rows within groups.

The function is especially useful when we’re transforming tabular data into a more hierarchical or nested JSON structure for web APIs, reporting, or downstream processing. It helps pivot rows into a single JSON object, making the data more compact and easier to consume in applications that require JSON formats.

Syntax

This aggregate function takes two arguments: a key column and a value column. Like this:

json_group_object(key, value)

It collects all key-value pairs across the grouped rows and returns a single JSON object per group (or over the entire table if no grouping is applied).

Example

Let’s create and populate a table called sales with to see how json_group_object() works:

CREATE TABLE sales (
  region TEXT,
  product TEXT,
  revenue INTEGER
);

INSERT INTO sales VALUES
  ('East', 'Apple', 100),
  ('East', 'Banana', 80),
  ('East', 'Cherry', 60),
  ('East', 'Date', 70),
  ('West', 'Apple', 120),
  ('West', 'Banana', 90),
  ('West', 'Cherry', 95),
  ('West', 'Date', 110),
  ('North', 'Apple', 130),
  ('North', 'Banana', 100),
  ('North', 'Cherry', 85);

Example Query: Grouped JSON by Region

Suppose we want to produce a JSON object of {product: revenue} for each region:

SELECT
  region,
  json_group_object(product, revenue) AS product_revenue_json
FROM
  sales
GROUP BY
  region;

Result:

+--------+--------------------------------------------------+
| region |               product_revenue_json               |
+--------+--------------------------------------------------+
| North  | {"Apple":130,"Banana":100,"Cherry":85}           |
| East   | {"Apple":100,"Banana":80,"Cherry":60,"Date":70}  |
| West   | {"Apple":120,"Banana":90,"Cherry":95,"Date":110} |
+--------+--------------------------------------------------+

Here, we included the GROUP BY clause in order to group the results by region. We can see that each region’s sales have been consolidated into a clean JSON object.

Without Grouping

We can also use it without the GROUP BY clause to create a single JSON object for the entire dataset:

SELECT json_group_object(product, revenue) AS overall_product_revenue
FROM sales;

Result:

+-------------------------------------------------------------------------------------------------------------------------------------+
|                                                       overall_product_revenue                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------+
| {"Apple":100,"Banana":80,"Cherry":60,"Date":70,"Apple":120,"Banana":90,"Cherry":95,"Date":110,"Apple":130,"Banana":100,"Cherry":85} |
+-------------------------------------------------------------------------------------------------------------------------------------+

This document provides all product and revenue data, but no regions.

Quick Summary

  • json_group_object(key, value) creates a JSON object by aggregating rows into {key: value} pairs.
  • It’s especially useful when pivoting or exporting data in structured formats.
  • Use with GROUP BY to aggregate per group, or without it to aggregate the entire dataset.