Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Complex nested JSON-Structure

user-jaaqzMay 4 2023

Dear Oracle Community,

I am dealing with a request for a complex JSON structure using our data. After struggling with the issue for 2 weeks, I decided to address the question to the Oracle Community because I have no more ideas what I can do. I hope very much and would be very happy when someone can help. PS: I started with Oracle SQL about half a year ago, so please don't be too abstract.

Version:

  • Oracle SQL Developer Version 4.1.3.20.78
  • Java 1.8.0
  • Oracle Database 19c Enterprise

Requested JSON structure:

{
	"dataStatus": "<date>",
	"data": {
			"year1":{
				"country1": {
					"tot": "tot_val1",
					"children": [
						{
							"name": "A",
							"children": [
								{"name": "a", "value": val},
								{"name": "b", "value": val},
								{"name": "c", "value": val},
								{"name": "d", "value": val},
								{"name": "e", "value": val}
							]
						},
						{
							"name": "B",
							"children": [
								{"name": "f", "value": val},
								{"name": "g", "value": val},
								{"name": "h", "value": val},
								{"name": "i", "value": val} 
							]
						},
						{
							"name": "C",
							"children": [
								{"name": "j", "value": val},
								{"name": "k", "value": val},
								{"name": "l", "value": val}
							]
						},
						...
					]},
				"country2": {
					"tot": "tot_val2",
					"children": [
						{
							"name": "A",
							"children": [
								{"name": "a", "value": val},
								{"name": "b", "value": val},
								{"name": "c", "value": val},
								{"name": "d", "value": val},
								{"name": "e", "value": val}
							]
						},
						{
							"name": "B",
							"children": [
								{"name": "f", "value": val},
								{"name": "g", "value": val},
								{"name": "h", "value": val},
								{"name": "i", "value": val} 
							]
						},
						{
							"name": "C",
							"children": [
								{"name": "j", "value": val},
								{"name": "k", "value": 0},
								{"name": "l", "value": val}
							]
						},
						...
				"country3": { <A,B,C,D is repeated in each country and each have 5, 4, 3 and 5 entries>
				... 
				},
				...
			"year2" : { 
				<the same repeats for 4 other years, the last year has only two countries but this 		
				has been solved using a separate query and a UNION ALL while creating the original 	
				data table> 
			}
		}
	}

Table structure:

  • Example with just 3 countries (there are about 20 in the original data set)
  • Only two years are given when there are actually 5 different years
  • The numbers are made up but reflect the table structure
CREATE TABLE proj4 (
year varchar2(4) nullable yes,
country varchar2(20) nullable yes,
name1 varchar2(1) nullable yes,
name2 varchar2(1) nullable yes,
val number nullable yes,
tot number nullable yes
);

INSERT INTO proj4 VALUES ("1990", "Canada", "A", "a", 600, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "A", "b", 7700, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "A", "c", 11000, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "A", "d", 500, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "A", "e", 100, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "B", "f", 600, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "B", "g", 7700, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "B", "h", 11000, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "B", "i", 500, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "C", "j", 100, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "C", "k", 100, 3800000);
INSERT INTO proj4 VALUES ("1990", "Canada", "C", "l", 100, 3800000);
INSERT INTO proj4 VALUES ("1990", "Peru", "A", "a", 250, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "A", "b", 700, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "A", "c", 100, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "A", "d", 20, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "A", "e", 40, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "B", "f", 80, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "B", "g", 700, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "B", "h", 1000, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "B", "i", 50, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "C", "j", 24, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "C", "k", 39, 200000);
INSERT INTO proj4 VALUES ("1990", "Peru", "C", "l", 45, 200000);
INSERT INTO proj4 VALUES ("1990", "Romania", "A", "a", 400, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "A", "b", 430, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "A", "c", 500, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "A", "d", 110, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "A", "e", 58, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "B", "f", 86, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "B", "g", 40, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "B", "h", 77, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "B", "i", 5, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "C", "j", 204, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "C", "k", 569, 500000);
INSERT INTO proj4 VALUES ("1990", "Romania", "C", "l", 669, 500000);

INSERT INTO proj4 VALUES ("2014", "Canada", "A", "a", 400, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "A", "b", 8700, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "A", "c", 12300, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "A", "d", 250, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "A", "e", 80, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "B", "f", 460, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "B", "g", 5700, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "B", "h", 9000, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "B", "i", 320, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "C", "j", 80, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "C", "k", 21, 3800000);
INSERT INTO proj4 VALUES ("2014", "Canada", "C", "l", 110, 3800000);
INSERT INTO proj4 VALUES ("2014", "Peru", "A", "a", 350, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "A", "b", 1100, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "A", "c", 250, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "A", "d", 86, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "A", "e", 55, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "B", "f", 120, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "B", "g", 860, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "B", "h", 1440, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "B", "i", 46, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "C", "j", 22, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "C", "k", 69, 200000);
INSERT INTO proj4 VALUES ("2014", "Peru", "C", "l", 65, 200000);
INSERT INTO proj4 VALUES ("2014", "Romania", "A", "a", 500, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "A", "b", 530, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "A", "c", 670, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "A", "d", 91, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "A", "e", 48, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "B", "f", 106, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "B", "g", 75, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "B", "h", 193, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "B", "i", 11, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "C", "j", 178, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "C", "k", 899, 500000);
INSERT INTO proj4 VALUES ("2014", "Romania", "C", "l", 719, 500000);
....

SQL Code so far:

SELECT 
	JSON_OBJECT( 
		KEY 'dataStatus' VALUE TO_CHAR(CURRENT_DATE, 'dd.mm.yyyy') , 
		KEY 'data' VALUE JSON_OBJECTAGG( 
			year VALUE JSON_OBJECTAGG( 
				KEY country VALUE json_tab 
				RETURNING CLOB ) 
			RETURNING CLOB ) 
		RETURNING CLOB )
FROM
(SELECT 
	year ,
	country ,
	JSON_OBJECT( 
		KEY 'tot' VALUE tot , 
		KEY 'children' VALUE JSON_ARRAY( 
			JSON_OBJECT( 
				KEY 'name' VALUE name1, 
				KEY 'children' VALUE JSON_ARRAYAGG( 
					JSON_OBJECT( 
						KEY 'name' VALUE name2, 
						KEY 'value' VALUE val ) 
						RETURNING CLOB ) 
					RETURNING CLOB ) 
				RETURNING CLOB ) 
			RETURNING CLOB ) AS json_tab
FROM 
	proj4
GROUP BY 
	year ,
	country ,
	tot ,
	name1
)
GROUP BY 
	year 
;

Changing the JSON_ARRAY() into a JSON_ARRAYAGG() did not work, resulting in the famous ORA-00937: "not a single-group group function". But I do not know where I can add further groups considering the affected level.

In case you need further information, please let me know and I will see if and how I can provide it.

Again thanks to anybody who can tell me what I am doing wrong and can point me to the right direction!
This post has been answered by mathguy on May 4 2023
Jump to Answer
Comments
Post Details
Added on May 4 2023
19 comments
10,888 views
5 mentions