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. 

R Dataframe in the eyes of SQL developer.

R has gained lot of momentum in the last few years  for Data Science. At first , for a SQL professional , this may be bit daunting ; however there are lot of similarities  between the RDBMS concepts and R concepts , that will make the learning curve tad easier. One of the similarity is the data frame. 


Data frame is one of the important component in R to capture the data from the external data sources ( aka importing from CSV , loading from RDMS  , and so on ) . 

It is conceptually same as the a table in a RDBMS system.

In the following , I have created a data frame with  3 elements and 6 rows. 
In RDBMS , this is the same as creating a table 'emp' and inserting 6 records. 

emp <- data.frame="" span="">
  name=c("Zahir","Farook","Hameed","Basheer","Aslam","Suhaib"),
  deptno=c(10,20,30,30,20,20),
 city=c("Monroe","Trichy","Kilakarai","Kilakarai","Chennai","Chennai"))  
















When  the data frame is referenced at the prompt , it returns the entire data set. This is similar to "SELECT * FROM EMP",




The function "rbind" is used to insert a record into the existing dataset. 
This is similar to "INSERT INTO EMP values ('Karady' , 100 , 'Colombo') "

emp <- arady="" data.frame="" deptno="c(100),city=c(" emp="" name="c(" olombo="" rbind="" span="">

 

The function "nrow" is used to get the record count of the dataset.
This is similar to " SELECT count(*) from EMP". 


The function "ncol" is used to get the record count of the columns.
This is similar to " SELECT count(*) from information_schema.columns where table_name =EMP'" .


 With the following example , we are filtering the records that have deptno = 30 . This is similar to
"SELECT *  FROM EMP WHERE DEPTNO= 30'.



 We can add , additional filter with the pipe function . Pipe is used for 'OR' condition. 
This is similar to "SELECT *  FROM EMP WHERE DEPTNO= 30 ORCITY ='Chennai'.





As we can see , there  are lot of similarites in with the concept of Table (tuple) and the dataframe. 
This could be a starting point to get familiar with R  for a SQL professional . 

I understand , I have just scratched the surface on the data frame and its functions.

As of now , Oracle and MS SQL Server has incoprated 'R' into their offerings.

Comments Welcome.

Implementation difference between PostgreSQL and MS SQL Server in varchar with no length.

Today , I learned something new  in PostgreSQL. Surprised  to see the implementation difference between  PostgreSQL and MS SQL Server . 

In PostgreSQL and SQL Server , I can define a 'varchar' or 'char' datatype  with no width . In PostgreSQL , 'char' datatype with no length means char(1) , whereas 'varchar' datatype with no length means that it can accommodate any size  

Here is the example from PostgreSQL

 sales=# SELECT version();
                                                   version
 -----------------------------------------------------------------------------------------------------------
  PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04) 4.8.4, 64-bit
(1 row)

sales=# create table t( c char) ;
CREATE TABLE
 sales=# insert into t values ('AA');
ERROR:  value too long for type character(1)
sales=# insert into t values ('A');
INSERT 0 1
sales=# select * from t;
 c
---
 A
(1 row)


sales=# drop table t;
DROP TABLE
sales=#
sales=# create table t( c varchar) ;
CREATE TABLE
sales=# insert into t values ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
sales-# ;
INSERT 0 1
sales=# Select * from t;
                                           c
---------------------------------------------------------------------------------------
 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


Here is the example from SQL Server.


C:\>sqlcmd -W
1> use test
2> go
Changed database context to 'test'.
1> Select @@version ;
2> go

-
Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64)
        May 27 2016 15:06:08
        Copyright (c) Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 14393: )


(1 rows affected)
1> drop table t;
2> go
1> create table t( c char) ;
2> go
1> insert into t values ('AA');
2> go
Msg 8152, Level 16, State 14, Server DOCS-0961, Line 1
String or binary data would be truncated.
The statement has been terminated.
1> insert into t values ('A');
2> go

(1 rows affected)
1> Select * from t;
2> go
c
-
A

(1 rows affected)
1> drop table t ;
2> create table t( c varchar) ;
3> go
1> insert into t values ('AA');
2> go
Msg 8152, Level 16, State 14, Server DOCS-0961, Line 1
String or binary data would be truncated.
The statement has been terminated.
1> insert into t values ('A');
2> go

(1 rows affected)
1> Select * from t;
2> go
c
-
A

(1 rows affected)

1>

As can be seen from the script above , PostgresQL and SQL Server behave the same way when a column is defined 'char' with no length. And it is so drastically different when it comes to 'varchar' with no length. 

In Oracle , this is not allowed . Any 'char' or 'varchar' has to have a length defined . 

Learning something new every day. 

Rolling Back a DDL

Not all the RDBMS are same .  Even though , there have  similar syntax / features  , they differ how they handle transactions  / locking / DDLs . 

In Oracle , DDLs  does implicit commit;  But in SQL Server , the DDLs can be rollback , if necessary. 

Moral of the story - Every RDBMS has own way to doing things.  We can't assume , if a particular feature works in one database  , it will work in other database in the same way. 

Sometimes , it helps us to unlearn some of the stuff you have learnt for one database and go from basics. 

Here is an example . 

In Oracle 

SQL> Create Table emp
  2  ( id int ,
  3    name varchar(50)
  4    ) ;

Table created.

SQL>     alter table emp add sal numeric(9,2) ;

Table altered.

SQL> rollback;

Rollback complete.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(50)
 SAL                                                NUMBER(9,2)


In SQL Server


C:\>sqlcmd -W
1> use test
2> go
Changed database context to 'test'.
1> Create Table emp
2> ( id int ,
3>   name varchar(50)
4>   ) ;
5> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.
No views with schema binding reference table 'emp'.
1>
2>  begin transaction
3>     alter table emp add sal numeric(9,2) ;
4> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS
sal numeric no 5 9     2     yes (n/a) (n/a) NULL

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.

No views with schema binding reference table 'emp'.



1> rollback
2> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.
No views with schema binding reference table 'emp'.

How to execute Oracle Scheduler' Job from SQL Server.

In our work place , we use multiple DBMS to manage our data . Sometimes , we may need trigger a  job in one  DBMS  from another DBMS . If the these jobs are in the same DBMS / Environment , then these steps are straightforward . 

In some cases , we use Job Schedulers such as Autosys , Robot , etc to schedule the dependent jobs across various environments.  This will involve installing clients  / agents in all the environments . For some practical reasons  ( due to budgetary and regulatory concerns )  , this may not be feasible at times .

Sometimes , it is better to trigger the job in another DBMS from the host environment  . 

Let us say , we have a SQL Server as the host DBMS and we would like to trigger a Oracle ( Scheduler's ) Job.  

Here are the steps to achieve that . 

a) Create a linked server

b) Enable RPC out

c) Use Execute command to trigger the job. 

 Let us say , we have a job called 'LOAD_SALES_MONTHLY' in Oracle Database ( configured as 'ORACLE_LINKDB' in SQL Server ) .

The syntax for executing the oracle job from SQLPLUS

 exec dbms_scheduler.run_job('LOAD_SALES_MONTHLY')

The syntax for executing the above mentioned job from SQL Server is
 

Execute ( 'call dbms_scheduler.run_job(''LOAD_SALES_MONTHLY'')') AT ORACLE_LINKDB.

Note that , I am using "CALL" command to execute Oracle's Job.
 
 For steps a) and b) , please refer to one of my previous post (
http://mfzahirdba.blogspot.com/2012/04/execute-in-sql-server-2008.html) 



Note :
You can use the same syntax to execute Oracle's stored procedure / package .

Comments welcome.