Oracle 19 JSON Management

Christina Moore on April 5, 2023

Objective

A quick reference guide when writing Oracle PL/SQL code that manages JSON.

Abstract

We are all asked to routinely manage JSON data within Oracle databases and within PL/SQL. We are pulling the data from external sources via API or other reasons. JSON provides flexibility that classic Oracle tables do not. With the introduction of relational JSON with Oracle 23C, we will see more and more of this as our libraries expand.

JSON management has changed over the Oracle releases from 12, 19, to 21, and now 23. Yet each time I have to query or build JSON, I need a reference guide. I wanted something similar to a tree identification guide or a bird identification guide. The documentation within Oracle has improved over the years, as well as our ability to manage JSON data. These articles proves helpful:

Version	Link
23C	https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/index.html
21	https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/generation-of-json-data.html
19	https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/query-json-data.html
12	https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/json-in-oracle-database.html

table: Documentation for JSON Management within Oracle

Note about the data. I work a lot with spoken/written languages within Oracle APEX. The standard abbreviations for FSP Language Preferences have a dash or hyphen such as ‘fr-be’ and ‘en-gb’. But we can not support dashes within either Oracle fieldnames nor JSON field names, therefore we are using the underbar character (“_”). A quick replacement command will suit nicely.

Author’s Note

I am writing this article the same week that 23C released as a “free” product to developers. I will endeavor to update as we all move forward.

Hints

Squiggle Brackets { } are objects. Objects store data sets. Use Oracle’s json_object
Square Brackets [ ] are arrays. Array store child rows of related data. Use Oracle’s json_arrayagg
Close what you open with parenthesis
Start simple, build complexity one step at a time
Use on-line JSON formatters to double check your work

Query JSON – Object (No Array) + No Object Name

Sample Data

{"fr_be":"le chien", "nl_be":"een hond", "en_gb":"A dog"}

Observations

No nesting
No array
No Object Name

The Cheat

Follow the path. Simplest form.

Solution

select
	--d.description,
	js.french,
	js.dutch,
	js.english
from 
(
	select
	 '{"fr_be":"le chien", "nl_be":"een hond", "en_gb":"A dog"}' as description
	from dual
)	d, json_table 
		(description, '$' columns
			french	varchar2(128) 	path  '$.fr_be',
			dutch	varchar2(128) 	path  '$.nl_be',	
			english	varchar2(128) 	path  '$.en_gb'
		) js
;

Query JSON – Object (No Array) + Object Name

Sample Data

{"animal":{"fr_be":"le chien", "nl_be":"een hond", "en_gb":"A dog"}}

Observations

No nesting
No array
Has Object Name

The Cheat

Add the object name '$.animal'

Solution

select
	--d.description,
	js.french,
	js.dutch,
	js.english
from 
(
	select
	 '{"fr_be":"le chien", "nl_be":"een hond", "en_gb":"A dog"}' as description
	from dual
)	d, json_table 
		(description, '$.animal' columns
			french	varchar2(128) 	path  '$.fr_be',
			dutch	varchar2(128) 	path  '$.nl_be',	
			english	varchar2(128) 	path  '$.en_gb'
		) js
;

Query JSON – Object (no array) + Nested Object (no array)

Sample Data

{
  "animal": {
    "fr_be": "le chien",
    "details_fr": {
      "height": "haut",
      "color": "noir"
    },
    "nl_be": "een hond",
    "details_nl": {
      "height": "hoog",
      "color": "zwart"
    },
    "en_gb": "A dog",
    "details_en": {
      "height": "tall",
      "color": "black"
    }
  }
}

Observations

Has nesting
No arrays
Has Object Name

The Cheat

Add the phrase for nested path '$.details' within the JSON_Table structure.

Solution

select
	--d.description,
	js.french,
	js.dutch,
	js.english,
	js.height,
	js.color
from
(
	select
	 '{"animal": {"fr_be": "le chien","details": {"height": "haut", "color": "noir"}, "nl_be":"een hond",	"details": { "height": "hoog", "color": "zwart" }, "en_gb":"A dog", "details": {  "height": "tall", "color": "black" }}}' as description
	from dual
)	d, json_table
		(description, '$.animal' columns
			french	varchar2(128) 	path  '$.fr_be',
			dutch	varchar2(128) 	path  '$.nl_be',	
			english	varchar2(128) 	path  '$.en_gb',
			nested path '$.details' columns 
			(
				height	varchar2(128) path  '$.height',
				color	varchar2(128) 	path  '$.color'
			)		
		) js
;

Query JSON – Simple Array

Sample Data

[
  {
    "fr": "le chien",
    "nl": "een hond",
    "en": "A dog"
  },
  {
    "fr": "le chat",
    "nl": "een kat",
    "en": "A cat"
  }
]

Observations

No nesting
Has array
No Object Name

The Cheat

Add the notation for array with is a square bracket around asterisk: '$.[*]' within the JSON_Table structure.

Solution

select
	js.french,
	js.dutch,
	js.english
from
(
	select
		 '[{"fr":"le chien","nl":"een hond","en":"A dog"},{"fr":"le chat","nl":"een kat","en":"A cat"}]' as description from dual
)	d, json_table
		(description, '$[*]' columns
			french	varchar2(128) 	path  '$.fr',
			dutch	varchar2(128) 	path  '$.nl',	
			english	varchar2(128) 	path  '$.en'
) js;

Query JSON – Object + Nested Array

Sample Data

{
  "dog": [
    {
      "fr": "le chien",
      "nl": "een hond",
      "en": "A dog"
    }
  ]
}

Observations

No nesting
Has array
Has Array Name

The Cheat

Add the brackets and asterisk '$.dog[*]' within the JSON_Table structure.

Solution

select
	--d.description,
	js.french,
	js.dutch,
	js.english
from
(
	select
		 '{"dog": [{"fr": "le chien","nl":"een hond","en":"A dog"}]}' as description from dual
)	d, json_table
		(description, '$.dog[*]' columns
			french	varchar2(128) 	path  '$.fr',
			dutch	varchar2(128) 	path  '$.nl',	
			english	varchar2(128) 	path  '$.en'
) js;

Query JSON – Array + Nested Array(s)

Sample Data

{
  "animal": [
    {
      "fr_be": "le chien",
      "details_fr": [
        {
          "height": "haut",
          "color": "noir",
          "secondary": [
            {
              "pattern": "pommelé",
              "highlight": "brun"
            }
          ]
        }
      ],
      "nl_be": "een hond",
      "details_nl": [
        {
          "height": "hoog",
          "color": "zwart",
          "secondary": [
            {
              "pattern": "gevlekt",
              "highlight": "bruin"
            }
          ]
        }
      ],
      "en_gb": "A dog",
      "details_en": [
        {
          "height": "tall",
          "color": "black",
          "secondary": [
            {
              "pattern": "dappled",
              "highlight": "brown"
            }
          ]
        }
      ]
    }
  ]
}

Observations

Has nesting
Has array(s)
Has Array Name(s)

The Cheat

Add nested columns with the array notation of brakets + asterisk [*]

Solution

select
	--d.description,
	js.french,
	js.dutch,
	js.english,
	js.height,
	js.color,
	js.pattern,
	js.highlight
from
(
	select
	 '{"animal":[{"fr_be":"le chien","details":[{"height":"haut","color":"noir","secondary":[{"pattern":"pommelé","highlight":"brun"}]}],"nl_be":"een hond","details":[{"height":"hoog","color":"zwart","secondary":[{"pattern":"gevlekt","highlight":"bruin"}]}],"en_gb":"A dog","details":[{"height":"tall","color":"black","secondary":[{"pattern":"dappled","highlight":"brown"}]}]}]}' as description
	from dual
)	d, json_table
		(description, '$.animal[*]' columns
			french	varchar2(128) 	path  '$.fr_be',
			dutch	varchar2(128) 	path  '$.nl_be',	
			english	varchar2(128) 	path  '$.en_gb',
			nested path '$.details[*]' columns (
				height	varchar2(128) path  '$.height',
				color	varchar2(128) path  '$.color',
			nested path '$.secondary[*]' columns (
				pattern		varchar2(128) path  '$.pattern',
				highlight	varchar2(128) path  '$.highlight'
				)
			)	
		) js
;

Generate JSON – Simple Object

Observations

No nesting
No array
No Object Name

The Cheat

json_object serves as the squiggle brackets { }

Solution

select
	json_object (
		'documentBase64' 	VALUE '#BASE64#'
		,'name' 		VALUE  'sample_doc.pdf' 
		,'transformPdfFields'	VALUE 'true'
		,'fileExtension' 	VALUE 'pdf' 
		,'documentId' 		VALUE 1
		) x
from dual;

Resultant Data

{
  "documentBase64": "#BASE64#",
  "name": "sample_doc.pdf",
  "transformPdfFields": "true",
  "fileExtension": "pdf",
  "documentId": 1
}

Generate JSON – Object + Object Name

Observations

No nesting
No array
Has Object Name

The Cheat

json_object serves as the squiggle brackets { }. Add another!

Solution

select
json_object 
(
	'documents' value 
	(
	json_object 
		(
		'documentBase64' 	VALUE '#BASE64#'
		,'name' 		VALUE  'sample_doc.pdf' 
		,'transformPdfFields'	VALUE 'true'
		,'fileExtension' 	VALUE 'pdf' 
		,'documentId' 		VALUE 1
		) 
	)
) x
from dual;

Resultant Data

{
  "documents": {
    "documentBase64": "#BASE64#",
    "name": "sample_doc.pdf",
    "transformPdfFields": "true",
    "fileExtension": "pdf",
    "documentId": 1
  }
}

Generate JSON – Object + Nested Object

Observations

Has nesting
No array
Has Object Name

The Cheat

json_object serves as the squiggle brackets { }. Add fields with values.

Solution

select
json_object 
(
	'color'			value 'red',
	'size'			value 'small',
	'document' value 
	(
	json_object 
		(
		'documentBase64' 	VALUE '#BASE64#'
		,'name' 		VALUE  'sample_doc.pdf' 
		,'transformPdfFields'	VALUE 'true'
		,'fileExtension' 	VALUE 'pdf' 
		,'documentId' 		VALUE 1
		) 
	)
) x
from dual;

Resultant Data

{
  "color": "red",
  "size": "small",
  "documents": {
    "documentBase64": "#BASE64#",
    "name": "sample_doc.pdf",
    "transformPdfFields": "true",
    "fileExtension": "pdf",
    "documentId": 1
  }
}

Generate JSON – Simple Array

Observations

No nesting
Has array
No Object Name

The Cheat

json_arrayagg serves as the square brackets [].

Solution

select

	json_arrayagg 
	(
		json_object 
		(
			'primary_key'	value f.pk,
			'foreign_key'	value f.fk,
			'color'		value f.color,
			'tree'		value f.tree
		) --end inner json object 
	) --end array

from 
(
	select 
		pk,
		fk,
		color,
		tree
	from
		(
			select
				123 	pk,
				1	fk,
				'red' 	color,
				'maple' tree
			from dual
			union
			select
				124 	pk,
				1	fk,
				'yellow' color,
				'beech' tree
			from dual
		)
)f
	;

Resultant Data

[
  {
    "primary_key": 123,
    "foreign_key": 1,
    "color": "red",
    "tree": "maple"
  },
  {
    "primary_key": 124,
    "foreign_key": 1,
    "color": "yellow",
    "tree": "beech"
  }
]

Generate JSON – Simple Array

Observations

No nesting
Has array
No Object Name

The Cheat

json_arrayagg serves as the square brackets [].

Always have a json_object below a json_arrayagg.

Solution

select

	json_arrayagg 
	(
		json_object 
		(
			'primary_key'	value f.pk,
			'foreign_key'	value f.fk,
			'color'		value f.color,
			'tree'		value f.tree
		) --end inner json object 
	) --end array

from 
(
	select 
		pk,
		fk,
		color,
		tree
	from
		(
			select
				123 	pk,
				1	fk,
				'red' 	color,
				'maple' tree
			from dual
			union
			select
				124 	pk,
				1	fk,
				'yellow' color,
				'beech' tree
			from dual
		)
)f
	;

Resultant Data

[
  {
    "primary_key": 123,
    "foreign_key": 1,
    "color": "red",
    "tree": "maple"
  },
  {
    "primary_key": 124,
    "foreign_key": 1,
    "color": "yellow",
    "tree": "beech"
  }
]

Generate JSON – Object with Nested Array

Observations

Has nesting
Has array
Has Object Name

The Cheat

json_arrayagg serves as the square brackets []. Get bold and keep neat.

Always have a json_object below a json_arrayagg.

Solution

select
	json_object
	(
		'description'	value description,
		'forest_pk'	value data.fk,
		'trees'		value json_arrayagg 
		(
				json_object 
				(
					'tree'		value tree,
					'color'		value color,
					'tree_pk'	value pk
				) -- close object
		) -- close array
	) -- close outer object
from (
	with forest as (
		select 
			1 pk,
			'Vermont' description
		from dual
		)
		select 
			t.pk,
			t.fk,
			f.description,
			t.color,
			t.tree
		from
			(
				select
					123 	pk,
					1	fk,
					'red' 	color,
					'maple' tree
				from dual
				union
				select
					124 		pk,
					1		fk,
					'yellow'	color,
					'beech' 	tree
				from dual
			) t
			left join forest f on f.pk = t.fk
) data
group by description,fk
;

Resultant Data

{
  "description": "Vermont",
  "forest_pk": 1,
  "trees": [
    {
      "tree": "maple",
      "color": "red",
      "tree_pk": 123
    },
    {
      "tree": "beech",
      "color": "yellow",
      "tree_pk": 124
    }
  ]
}

Generate JSON – Array with Nested Array

Observations

Has nesting
Has array
Has Object Name

The Cheat

json_arrayagg serves as the square brackets []. Get bold and keep neat.

Always have a json_object below a json_arrayagg.

Solution

select
	json_arrayagg 
	( 
		json_object 
		(
			'description'	value description,
			'forest_pk'	value data.fk,
			'trees'		value json_arrayagg 
			(
					json_object 
					(
						'tree'		value tree,
						'color'		value color,
						'tree_pk'	value pk
					) -- close object
			) -- close inner array
		) -- outer object
	) -- close outer array
from (
	with forest as (
		select 
			1 pk,
			'Vermont' description
		from dual
		union
		select 
			2 pk,
			'Belgium' description
		from dual		
		)
		select 
			t.pk,
			t.fk,
			f.description,
			t.color,
			t.tree
		from
			(
				select
					123 	pk,
					1	fk,
					'red' 	color,
					'maple' tree
				from dual
				union
				select
					124 	pk,
					1	fk,
					'yellow' color,
					'beech' tree
				from dual
				union
				select
					123 	pk,
					2	fk,
					'brown' color,
					'oak'	tree
				from dual
			) t
			left join forest f on f.pk = t.fk
) data
group by description,fk
;

Resultant Data

[
  {
    "description": "Belgium",
    "forest_pk": 2,
    "trees": [
      {
        "tree": "oak",
        "color": "brown",
        "tree_pk": 123
      }
    ]
  },
  {
    "description": "Vermont",
    "forest_pk": 1,
    "trees": [
      {
        "tree": "maple",
        "color": "red",
        "tree_pk": 123
      },
      {
        "tree": "beech",
        "color": "yellow",
        "tree_pk": 124
      }
    ]
  }
]

Special thanks to my colleague Eli Duvall!

Category: APEX
Tag: apex, oracle, plsql, sql

Posted by Christina Moore

First database application during undergrad years: 1985. First full-time work in Oracle db: 1995 at FedEx in the land of international shipping and regulatory compliance. First APEX application: 2008 with a U.S. based wholesale grocery distribution outfit (logistics, finance, and regulatory compliance). First commercial APEX application: 2012 (time tracking/invoices, grant management/finances/regulatory compliance. There seems to be a trend with finance, regulatory compliance, logistics.

All Posts Website