JSON to SQL Objects: A Deep Dive

Introduction

JSON (JavaScript Object Notation) has become a popular data format for exchanging and storing data between web servers, web applications, and mobile apps. However, when dealing with structured data in SQL databases, we often need to convert it from JSON to SQL objects. This process involves mapping the JSON keys to SQL columns and handling complex JSON structures.

In this article, we’ll explore how to achieve this conversion using Oracle’s built-in JSON_TABLE function and bulk collect the resulting rows into a collection of SQL objects.

Background

Before diving into the code, let’s review some essential concepts:

• Objects in Oracle: In Oracle, an object is a custom data type that represents a collection of values, similar to a struct or class. Objects are used to represent complex data structures, such as JSON documents.
• JSON_TABLE function: The JSON_TABLE function in Oracle converts a JSON document into a relational table based on the provided mapping rules.
• Bulk collect: Bulk collect is a technique used to gather multiple rows from a query result set into a single collection of objects.

Types

To illustrate this concept, let’s define two types:

CREATE OR REPLACE TYPE json_record_type AS OBJECT(
  id    NUMBER,
  name  VARCHAR2(50),
  age   NUMBER
);

CREATE OR REPLACE TYPE json_table_type AS TABLE OF json_record_type;

These types represent the structure of our JSON data. The json_record_type type represents an individual record, while the json_table_type type represents a collection of these records.

Conversion

Now that we have defined our types, let’s create some sample JSON data:

DECLARE
  json_data CLOB := '{ "data": [ {"id": 1, "name": "John", "age": 30}, {"id": 2, "name": "Alice", "age": 25} ] }';
  json_obj json_table_type;
BEGIN
  SELECT * BULK COLLECT INTO json_obj
  FROM JSON_TABLE(json_data, '$.data[*]' COLUMNS ( ID NUMBER PATH '$.id', name VARCHAR2(50) PATH '$.name', age NUMBER PATH '$.age' ) );
  FOR i IN 1..json_obj.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      'ID: ' || json_obj(i).id
      || ', Name: ' || json_obj(i).name
      || ', Age: ' || json_obj(i).age
    );
  END LOOP;
END;
/

In this code:

• We first declare a CLOB variable to hold our sample JSON data.
• Then, we use the JSON_TABLE function to convert our JSON data into a relational table. The mapping rules specify how to extract values from the JSON document:
  • ID NUMBER PATH '$.id': Extracts the value of the “id” key as an integer.
  • name VARCHAR2(50) PATH '$.name': Extracts the value of the “name” key as a character string with a length of 50 characters.
  • age NUMBER PATH '$.age': Extracts the value of the “age” key as an integer.
• Finally, we bulk collect the resulting rows into a collection of SQL objects.

Fiddle

Here’s a complete code block that you can execute in your Oracle database:

-- Create sample JSON data
DECLARE
  json_data CLOB := '{ "data": [ {"id": 1, "name": "John", "age": 30}, {"id": 2, "name": "Alice", "age": 25} ] }';

-- Define types for the JSON data
CREATE OR REPLACE TYPE json_record_type AS OBJECT(
  id    NUMBER,
  name  VARCHAR2(50),
  age   NUMBER
);

CREATE OR REPLACE TYPE json_table_type AS TABLE OF json_record_type;

-- Convert sample JSON data to a SQL table using JSON_TABLE
DECLARE
  json_obj json_table_type;
BEGIN
  SELECT * BULK COLLECT INTO json_obj
  FROM JSON_TABLE(
           json_data,
           '$.data[*]'
           COLUMNS (
             ID   NUMBER       PATH '$.id',
             name VARCHAR2(50) PATH '$.name',
             age  NUMBER       PATH '$.age'
           )
         );

  -- Print the resulting rows as SQL objects
  FOR i IN 1..json_obj.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      'ID: ' || json_obj(i).id
      || ', Name: ' || json_obj(i).name
      || ', Age: ' || json_obj(i).age
    );
  END LOOP;
END;
/

This code block creates a sample JSON document, defines the types for its data, and then uses the JSON_TABLE function to convert it into a SQL table. The resulting rows are then printed as SQL objects.

Conclusion

Converting JSON objects to SQL tables is an essential task in many web applications, especially when integrating with databases that support JSON data types. In this article, we explored how to use Oracle’s built-in JSON_TABLE function and bulk collect the resulting rows into a collection of SQL objects using two custom types: json_record_type and json_table_type.

Last modified on 2024-03-06