In today’s data-driven world, JSON (JavaScript Object Notation) has emerged as a popular format for exchanging and storing data due to its simplicity and flexibility. As a result, database systems have evolved to include native support for JSON data types, enabling developers to work with JSON seamlessly within their applications. Oracle’s PL/JSON, an open-source package, has been widely used for JSON manipulation within PL/SQL environments. However, as organizations seek to embrace cloud-based solutions and modern database technologies, migrating from Oracle to Amazon Aurora PostgreSQL-Compatible Edition has become a preferred choice for customers looking for an enterprise open source alternative. Aurora PostgreSQL offers high availability, automated backups, and enhanced security. Migrating to Aurora PostgreSQL allows businesses to take advantage of a robust and modern platform with seamless integration into the AWS Cloud.
PostgreSQL’s native JSONB data type offers powerful JSON manipulation functions, making it a viable alternative to PL/JSON. JSONB stores JSON data in binary format, leading to reduced storage requirements and enhanced query performance. Additionally, JSONB data can be indexed efficiently, facilitating rapid retrieval and filtering of JSON documents.
Although PL/JSON has been a reliable choice for JSON handling in Oracle’s PL/SQL environment, the move to Aurora PostgreSQL requires a transformation of the existing PL/SQL procedures. This involves rewriting PL/JSON-specific code to utilize PostgreSQL’s native JSONB functions and operators. To ensure a smooth migration, you must grasp the key differences between PL/JSON and JSONB.
In this post, we offer a comprehensive guide on migrating PL/JSON-based procedures to PostgreSQL’s JSONB without losing functionality. Through practical examples of conversion, we aim to improve your confidence in adapting existing code to this new environment.
In the following sections, we walk through several examples of PL/JSON methods and their JSONB equivalents in Aurora PostgreSQL. The examples also apply to Amazon Relational Database Service (Amazon RDS) for PostgreSQL.
Solution overview
We discuss the following key topics:
JSON object operations
JSON array operations
JSON path queries
JSON processing functions
At a high level, the solution steps are as follows:
Analyse the existing PL/JSON code base and identify the JSON objects, arrays, and path queries used in the procedures.
Map the PL/JSON structures to equivalent JSONB representations in PostgreSQL.
Replace PL/JSON method calls with appropriate PostgreSQL JSONB functions. We use functions like jsonb_build_object, jsonb_build_array, and others to perform equivalent operations on JSONB data.
The following diagram illustrates the architecture.
The architecture consists of the following components:
An Amazon Relational Database Service (Amazon RDS) for Oracle DB instance with existing PL/SQL code where JSON data is handled using PL/JSON methods
An Aurora PostgreSQL DB instance to deploy the migrated code, which natively supports JSONB data type and functions
[Optional Step] AWS Schema Conversion Tool (AWS SCT) deployed in an Amazon Elastic Compute Cloud (Amazon EC2) instance to convert the PL/SQL code to Aurora PostgreSQL equivalent code.
Prerequisites
Before proceeding with the migration process from PL/JSON to JSONB in PostgreSQL, make sure you have the following prerequisites in place:
A basic understanding of PL/SQL programming concepts and familiarity with the Oracle database environment. Additionally, some knowledge of PostgreSQL PL/PGSQL.
A fundamental understanding of JSON (JavaScript Object Notation) is essential for grasping the concepts presented in this post.
If you want to try installing PL/JSON on Oracle database, refer to the steps outlined in Install section.
To follow along with the examples and implement the migration process, you need access to an Aurora PostgreSQL instance with the latest minor version available for 14 and above or an RDS for PostgreSQL instance with the latest minor version available for 14 and above inside a VPC.
[Optional Step] AWS SCT, can be a valuable asset during the migration process. AWS SCT converts the code and helps identify potential schema and code compatibility issues between Oracle and Aurora PostgreSQL. Using AWS SCT can significantly streamline the migration process and minimize manual effort.
Working with JSON object operations
This example demonstrates various JSON object operations using PL/JSON in Oracle and their equivalent implementations using JSONB in Aurora PostgreSQL. It showcases how to create, modify, and remove key-value pairs in a JSON object, handle null values, and check for the existence of specific elements. We can see the difference between using PL/JSON’s methods like print, put, and remove and using PostgreSQL’s JSONB functions like jsonb_insert and – (minus operator) to achieve similar JSON object manipulation.
Oracle
We use the following code in Oracle:
We get the following output:
PostgreSQL
We use the following code in PostgreSQL:
The converted code is as follows:
Declare variables:
obj – The JSONB variable used to store a JSON object
l_obj_key_count – A BIGINT variable to hold the count of direct members in the JSON object
list – The JSONB variable used to store a JSON array
Initialize the obj variable:
The obj variable is initialized with a JSON object containing various data types, including numbers, null, Boolean, and nested arrays and objects
Print object:
The code raises a notice to print the obj variable in a formatted, human-readable manner using the jsonb_pretty function
Print object with to_char:
The code raises a notice to print the obj variable after converting it to a varchar using the jsonb_pretty function
Print object in compact way:
The code raises a notice to print the obj variable without formatting, resulting in a compact representation
Print object equivalent to print in compact way:
The code raises a notice to print the obj variable again, providing the same compact representation
Add an element to JSON object:
The code uses the jsonb_insert function to add a new element with key g and value a little string to the JSON object obj
Remove an element from JSON object:
The code removes the element with key g from the JSON object obj using the – (minus) operator
Print JSON object element count:
The code uses the jsonb_object_keys function to count the number of direct members in the JSON object obj and raises a notice to display the count
Test if element exists:
The code uses the ? operator to check if the element with key json is good exists in the JSON object obj
If the element does not exist, it is inserted using jsonb_insert, and a notice is raised to indicate that the element was added
We get the following output:
Working with JSON array operations
This example focuses on working with JSON arrays using PL/JSON in Oracle and their equivalent implementations using JSONB in Aurora PostgreSQL. It demonstrates how to build, append, and count elements in a JSON array, handle nested arrays and empty arrays, as well as remove elements using PL/JSON’s pljson_list and PostgreSQL’s JSONB functions like jsonb_build_array, jsonb_array_length, and – (minus operator). We demonstrate the similarities and differences between PL/JSON’s array handling and JSONB’s array manipulation.
Oracle
We use the following Oracle code:
We get the following output:
PostgreSQL
We use the following PostgreSQL code:
The converted code is as follows:
Declare variables:
obj – The JSONB variable used to store a JSON object
l_obj_key_count – A BIGINT variable to hold the count of direct members in the JSON object
list – The JSONB variable used to store a JSON array
Initialize the obj variable:
The obj variable is initialized with a JSON object containing two key-value pairs: “a”: null and “b”: 12.243
Build and print array list:
The code initializes an empty JSON array list
It uses the jsonb_build_array function to append two JSON objects to the array
The code raises a notice to print the JSON array list
Print list with empty array and nested lists:
The code uses the jsonb_build_array function to create a JSON array with various elements, including an empty array and nested arrays
It raises a notice to print the JSON array list
Count of direct members in JSON list:
The code uses the jsonb_array_length function to calculate the count of direct members in the JSON array list
It raises a notice to display the count
Print JSON object with nested JSON and an array:
The code uses the jsonb_insert function to add two new elements to the JSON object obj
The elements are a nested JSON object with key nested json and a JSON array with key an array
It raises a notice to print the updated JSON object obj
Print JSON object with support for dates to string:
The code uses the jsonb_insert function to add a new element with key a date to the JSON object obj
The value for the key is converted from a PostgreSQL date type to a JSONB string representation using to_jsonb
It raises a notice to print the updated JSON object obj
Convert JSONB date to PostgreSQL date:
The code retrieves the value associated with the key a date from the JSON object obj
It converts the JSONB date representation back to a PostgreSQL date using the to_date function and raises a notice to display the result
We get the following output:
Working with JSON path queries
This example explores JSON path queries using PL/JSON in Oracle and their equivalent implementations using JSONB in Aurora PostgreSQL. It showcases how to access specific elements in a JSON object and array using dot notation and array indexes with PL/JSON’s exist method. We show how to achieve the same result with PostgreSQL’s JSONB functions like jsonb_path_query and jsonb_set, which use the SQL/JSON path language for efficient querying of JSON data. This example emphasizes the difference in syntax and query capabilities between PL/JSON and JSONB.
Oracle
We use the following code in Oracle:
We get the following output:
PostgreSQL
We use the following code in PostgreSQL:
The converted code is as follows:
Initialize the obj variable:
The obj variable is initialized with a JSON object containing various nested elements and arrays
Get number at JSON path:
The code uses the jsonb_path_query function to retrieve the value at the JSON path expression $.c.d[2].e
A notice is raised to display the result
Add element at JSON path:
The code uses the jsonb_set function to add a new element with value 123 at the JSON path expression {c,d,2,e}
A notice is raised to display the updated obj after adding the element
Add array as element at JSON path:
The code creates an empty JSON object using jsonb_build_object
It uses jsonb_set to add an array with values [1,2,3] at the JSON path expression {a,1,data,value,0,1,myarray}
A notice is raised to display the updated obj after adding the array
JSONB set with existing and non-existing paths:
The code demonstrates that jsonb_set requires the key path to exist in the JSON object (obj) to add elements
It fills gaps with JSON null(s) if the path doesn’t exist but should be created
Remove element at JSON path:
The code uses the – (minus) operator to remove the element at the JSON path expression {c,d,2,e}
A notice is raised to display the updated obj after removing the element
Remove array of array at JSON path:
The code uses the – (minus) operator to remove the array of array at the JSON path expression {c,d,0}
A notice is raised to display the updated obj after removing the array of array
Remove null element at JSON path:
The code uses the – (minus) operator to remove the null element at the JSON path expression {c,d,0}
A notice is raised to display the updated obj after removing the null element
We get the following output:
Working with JSON processing functions
This example demonstrates various JSON processing functions in PostgreSQL using the JSONB data type. The main focus is on understanding how to view the JSON data as SQL Table. This example emphasizes the difference in syntax and query capabilities between PL/JSON and JSONB.
Oracle
We use the following code in Oracle:
We get the following output:
ID
DISPLAYNAME
QTY
XID
XTRA
0
Back
5
1
extra_1
0
Back
5
21
extra_21
2
Front
2
9
extra_9
2
Front
2
90
extra_90
3
Middle
9
5
extra_5
3
Middle
9
20
extra_20
PostgreSQL
We use the following PostgreSQL code:
The SQL statement utilizes various JSONB functions to extract and unnest JSON data, transforming it into tabular format. The converted code is as follows:
JSONB to record set conversion:
The SQL uses the jsonb_to_recordset function to convert a JSONB array into a record set. The JSONB array contains multiple JSON objects, each representing a product with an ID, display name, quantity, and an array of extras.
Unnesting the extras array:
The unnest(extras) function is used to unnest the extras JSON array, effectively expanding each element of the array into separate rows while keeping the other product attributes duplicated for each row.
JSONB to record conversion for extras:
The jsonb_to_record function is applied to the unnested JSON objects represented by y. This function converts each element of the extras array into separate records.
We get the following output:
ID
DISPLAYNAME
QTY
XID
XTRA
0
Back
5
1
extra_1
0
Back
5
21
extra_21
2
Front
2
9
extra_9
2
Front
2
90
extra_90
3
Middle
9
5
extra_5
3
Middle
9
20
extra_20
Conclusion
Migrating from PL/JSON to JSONB in Aurora PostgreSQL follows a structured process, leveraging the native JSONB data type and functions. By understanding the equivalents of PL/JSON methods using JSONB, you can have a seamless transition while leveraging the performance benefits of Aurora PostgreSQL. The examples in this post will guide you in rewriting your PL/SQL procedures to be compatible with Aurora PostgreSQL.
Remember to test thoroughly and adapt the examples to your specific use cases to achieve the best results.
Leave any thoughts or questions in the comments section.
About the Authors
Bhanu Ganesh Gudivada is a Database Consultant on the AWS Professional Services team at AWS and specializes in database migrations. He is helping and enabling customers to build high-available, cost-effective database solutions and migrate their commercial engines to the AWS cloud. Curious to learn and implement new technologies around the databases and orchestrate migrations through automation.
Rajeshkumar Sabankar is a Database Specialty Architect with Amazon Web Services. He works with internal Amazon customers to build secure, scalable and resilient architectures in AWS cloud and help customers perform migrations from on-premise databases to AWS RDS and Aurora Databases.
Santhosh Kumar Adapa is a Sr. Database Consultant with the AWS Professional Services team at AWS. He works as a database migration specialist to help Amazon customers in design and implementing scalable, secure, performant, and robust database solutions in the cloud.
Read MoreAWS Database Blog