DEV Community

SANDEEP KUMAR
SANDEEP KUMAR

Posted on

Oracle SQL: JSON Functions

#oracle #oraclesql #sql

1. Overview of Oracle JSON Functions

Oracle Database provides powerful built-in SQL functions to construct JSON data directly from relational tables. These functions allow developers to transform rows and columns into standard JSON objects and arrays efficiently.

2. Core JSON Functions Reference

JSON_OBJECT

  • Description: Converts SQL data into a key-value pair formatted as a JSON object.
  • Syntax: JSON_OBJECT('key_name' VALUE column_name)
  • Example: 
SELECT JSON_OBJECT('ENAME' VALUE ename)
FROM emp;
Enter fullscreen mode Exit fullscreen mode
  • Output: 
{"ENAME": "KING"}
Enter fullscreen mode Exit fullscreen mode

JSON_OBJECTAGG

  • Description: An aggregate function that groups multiple rows and combines them into a single JSON object document containing multiple key-value pairs.
  • Notes: IMPORTANT — Commonly used when you want to aggregate child rows into a single JSON payload per group or query result set.
  • Syntax: JSON_OBJECTAGG(key_expression VALUE value_expression)
  • Example: 
SELECT JSON_OBJECTAGG('ENAME' VALUE ename)
FROM emp;
Enter fullscreen mode Exit fullscreen mode
  • Output: 
{"ENAME": "KING", "ENAME": "BLAKE", "ENAME": "CLARK"}
Enter fullscreen mode Exit fullscreen mode

JSON_ARRAY

  • Description: Evaluates each expression and returns a JSON array containing the values for each row.
  • Syntax: JSON_ARRAY(column_name)
  • Example: 
SELECT JSON_ARRAY(ename)
FROM emp;
Enter fullscreen mode Exit fullscreen mode
  • Output: 
["KING"]
["BLAKE"]
["CLARK"]
Enter fullscreen mode Exit fullscreen mode

JSON_ARRAYAGG

  • Description: An aggregate function that converts an entire set of rows/information into a single JSON array containing all values.
  • Notes: IMPORTANT — Ideal for turning a multi-row result set into a single JSON array column in reporting or API development.
  • Syntax: JSON_ARRAYAGG(column_name)
  • Example: 
SELECT JSON_ARRAYAGG(ename)
FROM emp;
Enter fullscreen mode Exit fullscreen mode
  • Output: 
["KING", "BLAKE", "CLARK"]
Enter fullscreen mode Exit fullscreen mode

3. Reference Table Structure (EMP Table)

To practice the queries above, use the standard EMP table structure:

Column Name Data Type Description
EMPNO NUMBER(4) Employee ID (Primary Key)
ENAME VARCHAR2(10) Employee Name
JOB VARCHAR2(9) Job Role
SAL NUMBER(7,2) Salary

4. Interview Preparation & Revision Corner

  • Q: What is the main difference between JSON_ARRAY and JSON_ARRAYAGG?
    • Answer: JSON_ARRAY is a scalar function evaluated per row (producing a separate single-element JSON array for each row), whereas JSON_ARRAYAGG is an aggregate function that merges multiple rows into one comprehensive JSON array.
  • Q: When should you use JSON_OBJECTAGG instead of regular aggregation functions?
    • Answer: Use JSON_OBJECTAGG when you need to serialize relational query results dynamically into standard JSON document formats for web services, REST APIs, or NoSQL migrations directly within Oracle SQL.

Top comments (0)