O: Oracle JSON : Select Level 1 and Level 2 data

In the below example, we will create a sample table with JSON data stored in a CLOB variable.

We the write a query using JSON_VALUE to select level 1 and level 2 data from the table.

CREATE TABLE STUDENT_PROFILE (
	STUDENT_ROLL_NO    NUMBER,
	STANDARD	       NUMBER,  
	FIRST_NAME         VARCHAR2(100),
	LAST_NAME          VARCHAR2(100),
	LOCATION           VARCHAR2(100),
	STUDENT_DATA       CLOB
);

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10001,10,'Tom','Jackson','California','{  
  "student_roll_no" : 10001, 
  "standard" : 10,  
  "first_name" : "Tom", 
  "last_name" : "Jackson", 
  "location" : "California", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');


Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10002,10,'Alcaraz','A','Spain','{  
  "student_roll_no" : 10002, 
  "standard" : 10,  
  "first_name" : "Alcaraz", 
  "last_name" : "A", 
  "location" : "Spain", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10003,9,'Sasha','Zverev','Germany','{  
  "student_roll_no" : 10003, 
  "standard" : 9,  
  "first_name" : "Sasha", 
  "last_name" : "Zverev", 
  "location" : "Germany", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10004,9,'Rafael','Nadal','Spain','{  
  "student_roll_no" : 10004, 
  "standard" : 9,  
  "first_name" : "Rafael", 
  "last_name" : "Nadal", 
  "location" : "Spain", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10005,8,'Steffi','Graf','Germany','{  
  "student_roll_no" : 10005, 
  "standard" : 8,  
  "first_name" : "Steffi", 
  "last_name" : "Graf", 
  "location" : "Germany", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10006,8,'Lothar','Mathaeus','Germany','{  
  "student_roll_no" : 10006, 
  "standard" : 8,  
  "first_name" : "Lothar", 
  "last_name" : "Mathaeus", 
  "location" : "Germany", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10007,8,'Novak','Djoko','Serbia','{  
  "student_roll_no" : 10007, 
  "standard" : 8,  
  "first_name" : "Novak", 
  "last_name" : "Djoko", 
  "location" : "Serbia", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10008,5,'Boris','Becker','Germany','{  
  "student_roll_no" : 10008, 
  "standard" : 5,  
  "first_name" : "Boris", 
  "last_name" : "Becker", 
  "location" : "Germany", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');


Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10009,7,'Lionel','Messi','Brazil','{  
  "student_roll_no" : 10009, 
  "standard" : 7,  
  "first_name" : "Lionel", 
  "last_name" : "Messi", 
  "location" : "Brazil", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

Insert into STUDENT_PROFILE (STUDENT_ROLL_NO,STANDARD,FIRST_NAME,LAST_NAME,LOCATION,STUDENT_DATA) values (10010,7,'King','Kong','Jungle','{  
  "student_roll_no" : 10010, 
  "standard" : 7,  
  "first_name" : "King", 
  "last_name" : "Kong", 
  "location" : "Jungle", 
  "websites" : [ 
    { 
      "website" : "work", 
      "URL" : "https://www.theoraclereference.blogspot.com/" 
    }, 
    { 
      "website" : "tutorials", 
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/" 
    } 
  ], 
  "social_media" : [ 
    { 
      "website" : "twitter", 
      "link" : "https://twitter.com/digitalocean" 
    }, 
    { 
      "website" : "facebook", 
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting" 
    }, 
    { 
      "website" : "github", 
      "link" : "https://github.com/digitalocean" 
    } 
  ] 
}');

SELECT 
student_roll_no, standard, first_name, last_name, location,
JSON_VALUE(STUDENT_DATA, '$.first_name') jfirst_name,
json_value (
         STUDENT_DATA, 
         '$.websites[1].website'
       ) website,
json_value (
         STUDENT_DATA, 
         '$.websites[1].URL'
       ) url
FROM STUDENT_PROFILE
WHERE LOCATION = 'Germany';

Output is as below

STUDENT_ROLL_NO	STANDARD	FIRST_NAME	LAST_NAME	LOCATION	JFIRST_NAME')	WEBSITE	URL
10003	9	Sasha	Zverev	Germany	Sasha	tutorials	https://www.udemy.com/course/introduction-to-oracle-sql/
10005	8	Steffi	Graf	Germany	Steffi	tutorials	https://www.udemy.com/course/introduction-to-oracle-sql/
10006	8	Lothar	Mathaeus	Germany	Lothar	tutorials	https://www.udemy.com/course/introduction-to-oracle-sql/
10008	5	Boris	Becker	Germany	Boris	tutorials	https://www.udemy.com/course/introduction-to-oracle-sql/

Query to Select Level 2 JSON data

Update the json for Rafael Nadal Roll No 10004 as below

{   
  "student_roll_no" : 10004,  
  "standard" : 9,   
  "first_name" : "Rafael",  
  "last_name" : "Nadal",  
  "location" : "Spain",  
  "websites" : [  
    {  
      "website" : "work",  
      "URL" : "https://www.theoraclereference.blogspot.com/"  
    },  
    {  
      "website" : "tutorials",  
      "URL" : "https://www.udemy.com/course/introduction-to-oracle-sql/"  
    }  
  ],  
  "social_media" : [  
    {  
      "website" : "twitter",  
      "link" : "https://twitter.com/digitalocean"  
    },  
    {  
      "website" : "facebook",  
      "link" : "https://www.facebook.com/DigitalOceanCloudHosting"  
    },  
    {  
      "description" : "github",  
      "website" : "https://github.com/digitalocean"  
    }  
  ],
  "study_schedule" :
   {  
     "subject"  : "geography",
	 "study_dates" :
  [
   {
      "study_date" : "01-Jul-2024"
   },
   {
      "study_date" : "05-Jul-2024"
   },
   {
      "study_date" : "03-Jul-2024"
   },
   {
      "study_date" : "10-Jul-2024"
   },
   {
      "study_date" : "08-Jul-2024"
   }
  ]
  }  
}

Query to select the level 2 JSON data is as below

SELECT 
STUDENT_ROLL_NO, jt.*
FROM
STUDENT_PROFILE sp ,
JSON_TABLE (
         STUDENT_DATA,
        '$.study_schedule.study_dates[*]'
        COLUMNS (
          studyDate VARCHAR2(20) PATH '$.study_date'
        )
       ) as jt
WHERE STUDENT_ROLL_NO = 10004;

STUDENT_ROLL_NO	STUDYDATE
10004	01-Jul-2024
10004	05-Jul-2024
10004	03-Jul-2024
10004	10-Jul-2024
10004	08-Jul-2024

To order the data in the order as in the level 2 schedule

SELECT 
STUDENT_ROLL_NO,
row_number() over (partition by STUDENT_ROLL_NO order by STUDENT_ROLL_NO, jt.idx) sr_no,
 to_date(jt.studyDate,'DD-MON-YYYY') 
FROM
STUDENT_PROFILE sp ,
JSON_TABLE (
         STUDENT_DATA,
        '$.study_schedule.study_dates[*]'
        COLUMNS (
          idx for ordinality, studyDate VARCHAR2(20) PATH '$.study_date'
        )
       ) as jt
WHERE STUDENT_ROLL_NO = 10004;

STUDENT_ROLL_NO	SR_NO	TO_DATE(JT.STUDYDATE,'DD-MON-YYYY')
10004	1	01-JUL-24
10004	2	05-JUL-24
10004	3	03-JUL-24
10004	4	10-JUL-24
10004	5	08-JUL-24

To order the data in the order of the level 2 column

SELECT 
STUDENT_ROLL_NO,
row_number() over (partition by STUDENT_ROLL_NO order by STUDENT_ROLL_NO, jt.idx) sr_no,
 to_date(jt.studyDate,'DD-MON-YYYY') study_date
FROM
STUDENT_PROFILE sp ,
JSON_TABLE (
         STUDENT_DATA,
        '$.study_schedule.study_dates[*]'
        COLUMNS (
          idx for ordinality, studyDate VARCHAR2(20) PATH '$.study_date'
        )
       ) as jt
WHERE STUDENT_ROLL_NO = 10004
ORDER BY study_date;

STUDENT_ROLL_NO	SR_NO	STUDY_DATE
10004	1	01-JUL-24
10004	3	03-JUL-24
10004	2	05-JUL-24
10004	5	08-JUL-24
10004	4	10-JUL-24

To Update a JSON value using JSON_MERGEPATCH

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"subject":"history"}}')
WHERE STUDENT_ROLL_NO = 10004;

1 rows updated.

Below link gives examples of some JSON_MERGEPATCH updates.

Oracle JSON_MERGEPATCH updates

To select level 1 and level 2 JSON data

SELECT JSON_VALUE(STUDENT_DATA, '$.study_schedule.subject'), JSON_VALUE(STUDENT_DATA, '$.study_schedule.study_dates[1].study_date')
FROM STUDENT_PROFILE
WHERE STUDENT_ROLL_NO = 10004;

SUBJ	DATE_1
history	05-Jul-2024

To update level 2 data with JSON_MERGEPATCH.

The statement gives 1 row updated, but the json does not show the updated value.

If someone can please share the way to update level 2 data with json_mergepatch it will be great.

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates[1]":{"study_date":"31-JUL-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004
AND JSON_VALUE(STUDENT_DATA, '$.study_schedule.study_dates[1].study_date') = '05-Jul-2024';

This will add a new json tag study_dates[1] to the existing JSON under study_schedule main tag. We can use JSON_TRANSFORM to update an existing JSON tag as follows.

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_TRANSFORM(STUDENT_DATA, SET '$.study_schedule.study_dates[0].study_date' = '31-DEC-2099')
WHERE STUDENT_ROLL_NO = 10004
AND JSON_VALUE(STUDENT_DATA, '$.study_schedule.study_dates[0].study_date') = '03-Jul-2024';

Thought for the day

One who is slow to anger is better than the mighty

and one whose temper is controlled than one who captures a city

Proverbs 16:32

Give me that gift Lord.

God's Word for the day

True and False Wisdom

The whole of wisdom is fear of the Lord,

and in all wisdom there is the fulfillment of the law.

The knowledge of wickedness is not wisdom

nor is there prudence in the counsel of sinners

There is a cleverness that is detestable

and there is a fool who merely lacks wisdom

Better are the God-fearing who lack understanding

than the highly intelligent who transgress the law.

Sirach 19:20-24

Navigation Bar

Friday, June 7, 2024

Oracle JSON : Select Level 1 and Level 2 data

Query to Select Level 2 JSON data

To order the data in the order as in the level 2 schedule

To order the data in the order of the level 2 column

To Update a JSON value using JSON_MERGEPATCH

1 comment: