Navigation Bar

Friday, June 7, 2024

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;
SUBJDATE_1
history05-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.
at
Labels:

1 comment:

Subscribe to: Post Comments (Atom)