JSON Aggregation in Oracle AI 26 aI and SQL Server 2025

 

If you have been following the latest releases from major vendors, three keywords are dominating the conversation: AI, Vector, and JSON.

In this post, we’ll take a look at a powerful addition to the modern SQL toolkit: the JSON_OBJECTAGG function. While major DBMS platforms have supported JSON for years, the latest iterations of Oracle (23ai) and SQL Server (2025) have introduced this function to streamline how we aggregate and transform relational data into structured JSON objects.

Think of JSON_OBJECTAGG as the JSON-specific evolution of LISTAGG (Oracle) or STRING_AGG (SQL Server). While the latter functions concatenate strings with a delimiter, JSON_OBJECTAGG takes a key-value pair and aggregates them into a single, valid JSON object.

Let's look at a Point of Sale (POS) table where we want to group sales dates by their price points for specific products.

Here is the example in SQL Server

SQL Server

1> select @@version

2> go

-

Microsoft SQL Server 2025 (RC1) - 17.0.925.4 (X64)

        Sep  9 2025 17:31:28

        Copyright (C) 2025 Microsoft Corporation

        Enterprise Evaluation Edition (64-bit) on Windows Server 2025 Standard 10.0 <X64> (Build 26100: ) (Hypervisor)

(1 rows affected)

In the example , the table 'POS' stores the point of sale data for products. 

We would like to get the sales data ( date , price) into a json by the product code.

1>

2> select * from POS;

3> go

product_code pos_date   price

------------ --------   -----

001 2005-12-01  100.00

001 2025-12-01  799.00

002 2025-12-01  57.00

(3 rows affected)

1>     Select product_code  , JSON_OBJECTAGG(t.price:t.pos_date) AS  SalesData

2>     FROM POS t

3>     group by product_code

4> go

product_code SalesData

------------ ---------

001 {"100.00":"2005-12-01","799.00":"2025-12-01"}

002 {"57.00":"2025-12-01"}

(2 rows affected)

Oracle

SQL> Select banner from v$version

BANNER                                                                                              

--------------------------------------------------------------------------------------------------- 

Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems   

Elapsed: 00:00:00.007

SQL> Select product_code  , JSON_OBJECTAGG(key to_char(t.price)  value t.pos_date) AS  SalesData

     FROM POS t

     group by product_code

PRODUCT_CODE SALESDATA                                                           

------------ ------------------------------------------------------------------- 

001          {"100":"2005-01-12T00:00:00","799":"2025-01-12T00:00:00"}           

002          {"57":"2025-12-01T00:00:00"}                                       

Elapsed: 00:00:00.009

2 rows selected.