Migrate from Oracle PL/JSON to Amazon Aurora PostgreSQL JSONB

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:

1. Analyse the existing PL/JSON code base and identify the JSON objects, arrays, and path queries used in the procedures.
2. Map the PL/JSON structures to equivalent JSONB representations in PostgreSQL.
3. 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:

declare
  obj  pljson;
  list pljson_list;
begin

  obj := pljson('
    {
      "a": null,
      "b": 12.243,
      "c": 2e-3,
      "d": [true, false, "abdc", [1,2,3]],
      "e": [3, {"e2":3}],
      "f": {
        "f2":true
      }
    }');

  dbms_output.put_line('Print Object: ');
  obj.print;

  dbms_output.put_line('Print Object with to_char: ');
  dbms_output.put_line(obj.to_char);

  dbms_output.put_line('Print Object in compact way: ');
  obj.print(false);

  dbms_output.put_line('Print Object equivalent to print in compact way: ');
  dbms_output.put_line(obj.to_char(false));

  obj.put('g', 'a little string');

  dbms_output.put_line('Add an element to Json Object: ');
  dbms_output.put_line(obj.to_char(false));

  obj.remove('g');

  dbms_output.put_line('Remove an element from Json Object: ');
  dbms_output.put_line(obj.to_char(false));

  dbms_output.put_line('Print Json Object element count: ');
  dbms_output.put_line(obj.count);

  if not obj.exist('json is good') then
    obj.put('json is good', 'Yes!');
    if obj.exist('json is good') then
      obj.print;
      dbms_output.put_line('Test if element exists: ');
      dbms_output.put_line('Yes, Element Found!');
    end if;
  end if;

exception
  when others then
    dbms_output.put_line('In when others of .. ' || sqlerrm);
    dbms_output.put_line('In when others of print trace.. ' ||
                         dbms_utility.format_error_backtrace);
end;

We get the following output:

Print Object: 
{
  "a" : null,
  "b" : 12.243,
  "c" : 0.002,
  "d" : [true, false, "abdc", [1, 2, 3]],
  "e" : [3, {
    "e2" : 3
  }],
  "f" : {
    "f2" : true
  }
}

Print Object with to_char: 
{
  "a" : null,
  "b" : 12.243,
  "c" : 0.002,
  "d" : [true, false, "abdc", [1, 2, 3]],
  "e" : [3, {
    "e2" : 3
  }],
  "f" : {
    "f2" : true
  }
}

Print Object in compact way: 
{"a":null,"b":12.243,"c":0.002,"d":[true,false,"abdc",[1,2,3]],"e":[3,{"e2":3}],"f":{"f2":true}}

Print Object equivalent to print in compact way: 
{"a":null,"b":12.243,"c":0.002,"d":[true,false,"abdc",[1,2,3]],"e":[3,{"e2":3}],"f":{"f2":true}}

Add an element to Json Object: 
{"a":null,"b":12.243,"c":0.002,"d":[true,false,"abdc",[1,2,3]],"e":[3,{"e2":3}],"f":{"f2":true},"g":"a little string"}

Remove an element from Json Object: 
{"a":null,"b":12.243,"c":0.002,"d":[true,false,"abdc",[1,2,3]],"e":[3,{"e2":3}],"f":{"f2":true}}

Print Json Object element count: 
6

{
  "a" : null,
  "b" : 12.243,
  "c" : 0.002,
  "d" : [true, false, "abdc", [1, 2, 3]],
  "e" : [3, {
    "e2" : 3
  }],
  "f" : {
    "f2" : true
  },
  "json is good" : "Yes!"
}

Test if element exists: 
Yes, Element Found!

PostgreSQL

We use the following code in PostgreSQL:

do
$$
declare 
	obj jsonb;
	l_obj_key_count bigint;
	list jsonb;
begin

	obj := '
    {
      "a": null,
      "b": 12.243,
      "c": 2e-3,
      "d": [true, false, "abdc", [1,2,3]],
      "e": [3, {"e2":3}],
      "f": {
        "f2":true
      },
	  "a": 1
    }'::jsonb;
	
	raise notice 'Print Object: ';
	raise notice '%', jsonb_pretty(obj);
	
	raise notice 'Print Object with to_char: ';
	raise notice '%', jsonb_pretty(obj)::varchar;
      
	raise notice 'Print Object in compact way: ';
    	raise notice '%', obj;
  
  	raise notice 'Print Object equivalent to print in compact way: ';
  	raise notice '%', obj;
		
	obj := jsonb_insert(obj, '{g}', '"a little string"', true);
	
	raise notice 'Add an element to Json Object: ';
	raise notice '%', obj;
	
	obj := obj - 'g';
	raise notice 'Remove an element from Json Object: ';
	raise notice '%', obj;
	
	SELECT COUNT(*) INTO l_obj_key_count FROM jsonb_object_keys(obj);
	raise notice 'Print Json Object element count: ';
	raise notice '%', l_obj_key_count;
	
	-- test if an element exists
	if not obj ? 'json is good' then
		obj := jsonb_insert(obj, '{json is good}', '"Yes!"', true);
		if obj ? 'json is good' then
		  raise notice '%', obj;
		  raise notice 'Test if element exists: ';
		  raise notice '%', 'Yes, Element Found!';
		end if;
	end if;	 
end;
$$
language plpgsql;

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:

NOTICE:  Print Object: 
NOTICE:  {
    "a": 1,
    "b": 12.243,
    "c": 0.002,
    "d": [
        true,
        false,
        "abdc",
        [
            1,
            2,
            3
        ]
    ],
    "e": [
        3,
        {
            "e2": 3
        }
    ],
    "f": {
        "f2": true
    }
}

NOTICE:  Print Object with to_char: 
NOTICE:  {
    "a": 1,
    "b": 12.243,
    "c": 0.002,
    "d": [
        true,
        false,
        "abdc",
        [
            1,
            2,
            3
        ]
    ],
    "e": [
        3,
        {
            "e2": 3
        }
    ],
    "f": {
        "f2": true
    }
}

NOTICE:  Print Object in compact way: 
NOTICE:  {"a": 1, "b": 12.243, "c": 0.002, "d": [true, false, "abdc", [1, 2, 3]], "e": [3, {"e2": 3}], "f": {"f2": true}}

NOTICE:  Print Object equivalent to print in compact way: 
NOTICE:  {"a": 1, "b": 12.243, "c": 0.002, "d": [true, false, "abdc", [1, 2, 3]], "e": [3, {"e2": 3}], "f": {"f2": true}}

NOTICE:  Add an element to Json Object: 
NOTICE:  {"a": 1, "b": 12.243, "c": 0.002, "d": [true, false, "abdc", [1, 2, 3]], "e": [3, {"e2": 3}], "f": {"f2": true}, "g": "a little string"}

NOTICE:  Remove an element from Json Object: 
NOTICE:  {"a": 1, "b": 12.243, "c": 0.002, "d": [true, false, "abdc", [1, 2, 3]], "e": [3, {"e2": 3}], "f": {"f2": true}}

NOTICE:  Print Json Object element count: 
NOTICE:  6

NOTICE:  {"a": 1, "b": 12.243, "c": 0.002, "d": [true, false, "abdc", [1, 2, 3]], "e": [3, {"e2": 3}], "f": {"f2": true}, "json is good": "Yes!"}

NOTICE:  Test if element exists: 
NOTICE:  Yes, Element Found!
DO

Query returned successfully in 538 msec.

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:

declare 
  obj pljson;
  list pljson_list;
begin
  
  obj := pljson('
    {
      "a": null,
      "b": 12.243      
    }');  

  list := pljson_list(); 
  list.append(pljson('{"lazy construction": true}'));
  list.append(pljson_list('[1,2,3,4,5]'));

  dbms_output.put_line('Print Array List');
  list.print;
  
  list := pljson_list('[1,2,3,[3, []]]');
  dbms_output.put_line('Print List with Empty array and Nested Lists');
  list.print;
  
  dbms_output.put_line('Count of direct members in json list');
  dbms_output.put_line(list.count);

  obj.put('nested json', pljson('{"lazy construction": true}'));
  obj.put('an array', pljson_list('[1,2,3,4,5]'));
  dbms_output.put_line('Print Json Object with Nested Json and a Array');
  obj.print;

  
  obj.put('a date', pljson_ext.to_json_string(to_date('2017-10-21', 'YYYY-MM-DD')));
  dbms_output.put_line('Print Json Object with support for dates to string');
  obj.print;
  
  dbms_output.put_line(pljson_ext.to_date(obj.get('a date')));
  
exception
    when others then
      dbms_output.put_line('In when others of .. ' || sqlerrm);
      dbms_output.put_line('In when others of print trace.. ' || dbms_utility.format_error_backtrace);
end;

We get the following output:

Print Array List
[{
  "lazy construction" : true
}, [1, 2, 3, 4, 5]]

Print List with Empty array and Nested Lists
[1, 2, 3, [3, []]]

Count of direct members in json list
4

Print Json Object with Nested Json and a Array
{
  "a" : null,
  "b" : 12.243,
  "nested json" : {
    "lazy construction" : true
  },
  "an array" : [1, 2, 3, 4, 5]
}

Print Json Object with support for dates to string
{
  "a" : null,
  "b" : 12.243,
  "nested json" : {
    "lazy construction" : true
  },
  "an array" : [1, 2, 3, 4, 5],
  "a date" : "2017-10-21 00:00:00"
}

21-OCT-17

PostgreSQL

We use the following PostgreSQL code:

do
$$
declare 
	obj jsonb;
	l_obj_key_count bigint;
	list jsonb;
begin

	obj := '
    {
      "a": null,
      "b": 12.243
    }'::jsonb;
	
    list := '[]';
    list := list || jsonb_build_array('{"lazy construction": true}'::jsonb);
    list := list || jsonb_build_array('[1,2,3,4,5]'::jsonb);
  
    raise notice 'Print Array List';
	raise notice '%', list;
   
    list := jsonb_build_array(1,2,3,json_build_array(3,json_build_array()));
    raise notice 'Print List with Empty array and Nested Lists';
	raise notice '%', list;	
	
	raise notice 'Count of direct members in json list';
    raise notice '%', jsonb_array_length(list); 
      
    obj := jsonb_insert(obj, '{nested json}', '{"lazy construction": true}'::jsonb, true);
    obj := jsonb_insert(obj, '{an array}', jsonb_build_array(1,2,3,4,5)::jsonb, true);
    raise notice 'Print Json Object with Nested Json and a Array';
    raise notice '%', obj;  
    
	obj := jsonb_insert(obj, '{a date}', to_jsonb(to_date('2017-10-21','YYYY-MM-DD')), true);
    raise notice 'Print Json Object with support for dates to string';
	raise notice '%', obj;    
   
   raise notice '%', to_date(obj->>'a date','YYYY-MM-DD');  
end;
$$
language plpgsql;

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:

NOTICE:  Print Array List
NOTICE:  [{"lazy construction": true}, [1, 2, 3, 4, 5]]

NOTICE:  Print List with Empty array and Nested Lists
NOTICE:  [1, 2, 3, [3, []]]

NOTICE:  Count of direct members in json list
NOTICE:  4

NOTICE:  Print Json Object with Nested Json and a Array
NOTICE:  {"a": null, "b": 12.243, "an array": [1, 2, 3, 4, 5], "nested json": {"lazy construction": true}}

NOTICE:  Print Json Object with support for dates to string
NOTICE:  {"a": null, "b": 12.243, "a date": "2017-10-21", "an array": [1, 2, 3, 4, 5], "nested json": {"lazy construction": true}}

NOTICE:  2017-10-21
DO

Query returned successfully in 527 msec.

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:

declare
  obj  pljson;
  list pljson_list;
begin

  obj := pljson('{
      "a" : true,
      "b" : [1,2,"3"],
      "c" : {
        "d" : [["array of array"], null, { "e": 7913 }]
      }
    }');

  dbms_output.put_line('Get Number at path: c.d[3].e ');
  dbms_output.put_line(pljson_ext.get_number(obj, 'c.d[3].e'));

  pljson_ext.put(obj, 'c.d[3].e', 123);
  dbms_output.put_line('Add Element at path: c.d[3].e ');
  obj.print;

  obj := pljson();
  pljson_ext.put(obj,
                 'a[2].data.value[1][2].myarray',
                 pljson_list('[1,2,3]'));
  dbms_output.put_line('Add Array as Element at path: a[2].data.value[1][2].myarray ');
  obj.print;

  obj := pljson('{
      "a" : true,
      "b" : [1,2,"3"],
      "c" : {
        "d" : [["array of array"], null, { "e": 7913 }]
      }
    }');

  pljson_ext.remove(obj, 'c.d[3].e');
  dbms_output.put_line('Remove Element at Path: c.d[3].e ');
  obj.print;

  pljson_ext.remove(obj, 'c.d[1]');
  dbms_output.put_line('Remove Array of Array at Path: c.d[1] ');
  obj.print;

  pljson_ext.remove(obj, 'c.d[1]');
  dbms_output.put_line('Remove Null Element at Path: c.d[1] ');
  obj.print;

exception
  when others then
    dbms_output.put_line('In when others of .. ' || sqlerrm);
    dbms_output.put_line('In when others of print trace.. ' ||
                         dbms_utility.format_error_backtrace);
end;

We get the following output:

Get Number at path: c.d[3].e 
7913

Add Element at path: c.d[3].e 
{
  "a" : true,
  "b" : [1, 2, "3"],
  "c" : {
    "d" : [["array of array"], null, {
      "e" : 123
    }]
  }
}

Add Array as Element at path: a[2].data.value[1][2].myarray 
{
  "a" : [null, {
    "data" : {
      "value" : [[null, {
        "myarray" : [1, 2, 3]
      }]]
    }
  }]
}

Remove Element at Path: c.d[3].e 
{
  "a" : true,
  "b" : [1, 2, "3"],
  "c" : {
    "d" : [["array of array"], null]
  }
}

Remove Array of Array at Path: c.d[1] 
{
  "a" : true,
  "b" : [1, 2, "3"],
  "c" : {
    "d" : [null]
  }
}

Remove Null Element at Path: c.d[1] 
{
  "a" : true,
  "b" : [1, 2, "3"]
}

PostgreSQL

We use the following code in PostgreSQL:

do
$$
declare 
	obj jsonb;
	l_obj_key_count bigint;
	list jsonb;
begin	
  
  obj := '{
      "a" : true,
      "b" : [1,2,"3"],
      "c" : {
        "d" : [["array of array"], null, { "e": 7913 }]
      }
    }'::jsonb;
	
  /*
  get elements using a json path expression
  The jsonpath type implements support for the SQL/JSON path language in PostgreSQL to efficiently query JSON data
  Dot (.) is used for member access.
  Square brackets ([]) are used for array access.
  SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.
  */
  raise notice 'Get Number at path: c.d[3].e ';
  raise notice '%',jsonb_path_query(obj, '$.c.d[2].e');
  
  obj := jsonb_set(obj,'{c,d,2,e}', '123'::jsonb, true);
  raise notice 'Add Element at path: c.d[3].e ';
  raise notice '%', obj;
  
  /*
  if you provide an invalid path then an error is raised
  you can, however, specify a path that doesn't exists but should be created
  arrays are 0-indexed.
  gaps will be filled with json null(s)
  */
  obj := jsonb_build_object();
  obj := jsonb_set(obj,'{a,1,data,value,0,1,myarray}', jsonb_build_array(1,2,3)::jsonb, true);
  raise notice 'Add Array as Element at path: a[2].data.value[1][2].myarray ';
  raise notice '%', obj; 
  
  /*
  PostgreSQL Json_set second argument Key verified for a valid key path in the Obj, if it doesn't exists it won't create. 
  */
  
  obj := jsonb_build_object();
  obj := '{
		  "a" : [null, {
			"data" : {
			  "value" : [[null, {
				"myarray" : null
			  }]]
			}
		  }]
		}';
       
   obj := jsonb_set(obj,'{a,1,data,value,0,1,myarray}', jsonb_build_array(1,2,3)::jsonb, true);
  
   -- fill the holes
   obj := jsonb_set(obj,'{a,0}','"filler1"'::jsonb, true);
   obj := jsonb_set(obj,'{a,1,data,value,0,0}', '"filler2"'::jsonb, true);
  
  -- replace larger structures:
  obj := jsonb_set(obj,'{a,1,data}', '7913'::jsonb, true);
  
  obj := jsonb_build_object();
  obj := '{
      "a" : true,
      "b" : [1,2,"3"],
      "c" : {
        "d" : [["array of array"], null, { "e": 7913 }]
      }
    }';
	
  -- remove element
  obj := obj #- '{c,d,2,e}';
  raise notice 'Remove Element at Path: c.d[3].e ';
  raise notice '%', obj; 	    
  
  -- remove array of array
  obj := obj #- '{c,d,0}';
  raise notice 'Remove Array of Array at Path: c.d[1] ';
  raise notice '%', obj;
  
  -- remove null element
  obj := obj #- '{c,d,0}';
  obj := obj #- '{c}';
  raise notice 'Remove Null Element at Path: c.d[1] ';
  raise notice '%', obj;
  
end;
$$
language plpgsql;

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:

NOTICE:  Get Number at path: c.d[3].e 
NOTICE:  7913

NOTICE:  Add Element at path: c.d[3].e 
NOTICE:  {"a": true, "b": [1, 2, "3"], "c": {"d": [["array of array"], null, {"e": 123}]}}

NOTICE:  Add Array as Element at path: a[2].data.value[1][2].myarray 
NOTICE:  {}

NOTICE:  Remove Element at Path: c.d[3].e 
NOTICE:  {"a": true, "b": [1, 2, "3"], "c": {"d": [["array of array"], null, {}]}}

NOTICE:  Remove Array of Array at Path: c.d[1] 
NOTICE:  {"a": true, "b": [1, 2, "3"], "c": {"d": [null, {}]}}

NOTICE:  Remove Null Element at Path: c.d[1] 
NOTICE:  {"a": true, "b": [1, 2, "3"]}

DO

Query returned successfully in 522 msec.

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:

select * from table(pljson_table.json_table(
  '[
    { "id": 0, "displayname": "Back",  "qty": 5, "extras": [ { "xid": 1, "xtra": "extra_1" }, { "xid": 21, "xtra": "extra_21" } ] },
    { "id": 2, "displayname": "Front", "qty": 2, "extras": [ { "xid": 9, "xtra": "extra_9" }, { "xid": 90, "xtra": "extra_90" } ] },
    { "id": 3, "displayname": "Middle", "qty": 9, "extras": [ { "xid": 5, "xtra": "extra_5" }, { "xid": 20, "xtra": "extra_20" } ] }
  ]',
  pljson_varray('[*].id', '[*].displayname', '[*].qty', '[*].extras[*].xid', '[*].extras[*].xtra'),
  pljson_varray('id', 'displayname', 'qty', 'xid', 'xtra'),
  table_mode => 'nested'
));

We get the following output:

PostgreSQL

We use the following PostgreSQL code:

SELECT id, displayname, qty, xid, xtra FROM jsonb_to_recordset('[
    { "id": 0, "displayname": "Back",  "qty": 5, "extras": [ { "xid": 1, "xtra": "extra_1" }, { "xid": 21, "xtra": "extra_21" } ] },
    { "id": 2, "displayname": "Front", "qty": 2, "extras": [ { "xid": 9, "xtra": "extra_9" }, { "xid": 90, "xtra": "extra_90" } ] },
    { "id": 3, "displayname": "Middle", "qty": 9, "extras": [ { "xid": 5, "xtra": "extra_5" }, { "xid": 20, "xtra": "extra_20" } ] }
  ]'::jsonb) as x(id int, displayname varchar(100), qty int, extras jsonb[]), unnest(extras) y, jsonb_to_record(y) as z(xid int, xtra varchar(100));

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:

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.