In this post, we are going to cover how to run SQL on top of your JSON. It doesn't matter which data model you choose (SODA, hybrid or Duality Views), you can run queries on top of them.
To run SQL on top of JSON, we are going to use the dot notation. We need to explicitly tell which is the field we want to query. Also is mandatory to have an alias for the table we plan to use.
Let's run an example. Let's query the payment_status field and the reservation_id. We want to see if we have a customer who didn't pay. If you recall from the last chapter, the HOTEL_RESERVATIONS is a SODA collection. The JSON is stored inside of a column called data. Therefore we have to use the alias (h), the column (data) and finally the field to query:
select h.data.payment_status, h.data.reservation_id
2* from HOTEL_RESERVATIONS h;
PAYMENT_STATUS RESERVATION_ID
_________________ _________________
"paid" "1"
"paid" "2"
"paid" "3"
"paid" "4"
"paid" "5"
If you are wondering if there is any limitation with SQL and JSON, there is not! Let's run another example where we want to sum the aggregated income per country:
SQL> select h.data.guest_contact_info.address.country "Country",sum(h.data.total_cost) "Total Income"
2 from HOTEL_RESERVATIONS h
3 group by h.data.guest_contact_info.address.country
4* order by sum(h.data.total_cost) desc;
Country Total Income
___________ _______________
"Spain" 1632
"France" 1450
It is possible to run any SQL over JSON data but, what happens if the JSON is complex? We can run queries too! Let's use the collection restaurant_bookings. This JSON has an array called allergens. We can use the nested clause to see this data as relational:
SQL> select customers.*
2 from RESTAURANT_BOOKINGS nested data columns
3 (
4 guest_email varchar2(50),
5 board varchar2(10),
6 included_in_reservation varchar2(10) ,
7* nested allergens[*] columns (allergen path '$')) customers;
GUEST_EMAIL BOARD INCLUDED_IN_RESERVATION ALLERGEN
_____________________________ ________ __________________________ ___________
olivia.johnson@example.com full true gluten
olivia.johnson@example.com full true nuts
liam.patel@example.com full true milk
liam.patel@example.com full true eggs
When we normalize the JSON data, we are going to have an individual row per element in the array. In the same way, we can use the JSON_TABLE clause to have JSON data printed as relational. In this example we are querying the fields inside the guest_contact_info document.
SQL> select jt.*
2 from hotel_reservations, JSON_TABLE(data, '$' columns(
3 reservation_id varchar(5),
4 room_id varchar2(5),
5 checkin_date date,
6 checkout_date varchar2(15),
7 email varchar2(50) path '$.guest_contact_info.email',
8 phone varchar2(50) path '$.guest_contact_info.phone'
9* )) jt;
RESERVATION_ID ROOM_ID CHECKIN_DATE CHECKOUT_DATE EMAIL PHONE
_________________ __________ _______________ ________________ _____________________________ ___________
1 105 03-JUN-23 2023-06-07 mrodriguez@example.com 777-4231
2 315 15-JUN-23 2023-06-17 ethan.lee@example.com 123-8106
3 207 25-JUN-23 2023-06-30 olivia.johnson@example.com 987-1890
4 222 07-JUN-23 2023-06-17 liam.patel@example.com 123-8106
5 101 01-JUN-23 2023-06-05 john.smith@example.com 555-1234
Not all the JSON are always the same, that's one of the great benefits of using JSON. In our example, some customers had introduced some information which was optional. What happens if the data is not the same? There is no problem! This data will appear like null and the SQL query will run without problems. Check the columns type, quantity and details:
SQL> select jt.*
2 from hotel_reservations, JSON_TABLE(data, '$' columns(
3 reservation_id varchar(5),
4 room_id varchar2(5),
5 checkin_date date,
6 checkout_date varchar2(15),
7 email varchar2(50) path '$.guest_contact_info.email',
8 phone varchar2(50) path '$.guest_contact_info.phone',
9 nested path '$.additional_requests[*]' columns(
10 type varchar2(20),
11 quantity number,
12 details varchar2(50)
13 )
14* )) jt;
RESERVATION_ID ROOM_ID CHECKIN_DATE CHECKOUT_DATE EMAIL PHONE TYPE QUANTITY DETAILS
_________________ __________ _______________ ________________ _____________________________ ___________ ________________ ___________ ____________________________________
1 105 03-JUN-23 2023-06-07 mrodriguez@example.com 777-4231
2 315 15-JUN-23 2023-06-17 ethan.lee@example.com 123-8106
3 207 25-JUN-23 2023-06-30 olivia.johnson@example.com 987-1890
4 222 07-JUN-23 2023-06-17 liam.patel@example.com 123-8106
5 101 01-JUN-23 2023-06-05 john.smith@example.com 555-1234 extra_bed 1
5 101 01-JUN-23 2023-06-05 john.smith@example.com 555-1234 late_checkout Please arrange for a 2pm checkout
Another great capability of using SQL, is that we can change a specific value of the JSON document. For example, a customer wants to change a room. We can update that specific value with a simple update. For that we are going to use JSON_TRANSFORM:
SQL> SELECT h.data.reservation_id, h.data.room_id, h.data.guest_contact_info.email
2 from hotel_reservations h
3* where h.data.reservation_id='4';
RESERVATION_ID ROOM_ID GUEST_CONTACT_INFO
_________________ __________ ___________________________
"4" "222" "liam.patel@example.com"
SQL>
SQL> update hotel_reservations h
2 set h.data = JSON_TRANSFORM(data,
3 set '$.room_id'='105'
4 )
5* where h.data.reservation_id='4';
1 row updated.
SQL>
SQL> commit;
SQL> SELECT h.data.reservation_id, h.data.room_id, h.data.guest_contact_info.email
2 from hotel_reservations h
3* where h.data.reservation_id='4';
RESERVATION_ID ROOM_ID GUEST_CONTACT_INFO
_________________ __________ ___________________________
"4" "105" "liam.patel@example.com"
If we want to return a scalar SQL value from a JSON scalar value, we can use JSON_VALUE. In this example we want to be sure that the reservation_id we receive is a number:
SQL> select JSON_Value (data, '$.reservation_id' returning number) from HOTEL_RESERVATIONS;
JSON_VALUE(DATA,'$.RESERVATION_ID'RETURNINGNUMBER)
_____________________________________________________
1
2
3
4
5
Another query we could run, is to know how many customers have an additional request. For that we can use JSON_Exists, which will allow us to filter rows based on the content of a JSON document:
SQL> select count(1)
2 from HOTEL_RESERVATIONS
3* where JSON_Exists(data, '$.additional_requests');
COUNT(1)
___________
1
And we can go further! We can use JSON_Exists to query inside the array! In the next query, I want to get the customers in JSON format using JSON_SERIALIZE who have asked for an extra bed:
SQL> select JSON_SERIALIZE(h.data)
2 from HOTEL_RESERVATIONS h
3* where JSON_Exists(h.data, '$.additional_requests.type?(@ == "extra_bed")');
JSON_SERIALIZE(H.DATA)
_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{"_id":"6582fcc50aec6a389818a1f1","checkin_date":"2023-06-01","num_adults":2,"reservation_id":"5","hotel_id":"123","room_id":"101","payment_status":"paid","num_children":1,"guest_contact_info":{"email":"john.smith@example.com","phone":"555-1234","address":{"city":"Lyon","country":"France"}},"additional_requests":[{"type":"extra_bed","quantity":1},{"type":"late_checkout","details":"Please arrange for a 2pm checkout"}],"checkout_date":"2023-06-05","total_cost":800,"guest_name":{"first_name":"John","last_name":"Smith"}}
SQL>
And finally if we want to simplify the queries to any application or Business Intelligence user, we can create a view on top of that JSON data. Using this view, I can query the JSON format and they will think it is a standard table!
SQL> create view reservation_info as
2 select jt.*
3 from hotel_reservations, JSON_TABLE(data, '$' columns(
4 reservation_id varchar(5),
5 room_id varchar2(5),
6 checkin_date date,
7 checkout_date varchar2(15),
8 email varchar2(50) path '$.guest_contact_info.email',
9 phone varchar2(50) path '$.guest_contact_info.phone',
10 nested path '$.additional_requests[*]' columns(
11 type varchar2(20),
12 quantity number,
13 details varchar2(50)
14 )
15* )) jt;
SQL> select * from reservation_info;
RESERVATION_ID ROOM_ID CHECKIN_DATE CHECKOUT_DATE EMAIL PHONE TYPE QUANTITY DETAILS
_________________ __________ _______________ ________________ _____________________________ ___________ ________________ ___________ ____________________________________
1 105 03-JUN-23 2023-06-07 mrodriguez@example.com 777-4231
2 315 15-JUN-23 2023-06-17 ethan.lee@example.com 123-8106
3 207 25-JUN-23 2023-06-30 olivia.johnson@example.com 987-1890
4 105 07-JUN-23 2023-06-17 liam.patel@example.com 123-8106
5 101 01-JUN-23 2023-06-05 john.smith@example.com 555-1234 extra_bed 1
5 101 01-JUN-23 2023-06-05 john.smith@example.com 555-1234 late_checkout Please arrange for a 2pm checkout
But if you are not sure of all the fields available on the JSON, or you want to avoid to write the query before, you can use the Data Guide we learn before. With the Data Guide, we can automatically create this view!
SQL> DECLARE
2 dg CLOB;
3 BEGIN
4 SELECT JSON_DATAGUIDE(data,dbms_json.FORMAT_HIERARCHICAL,dbms_json.pretty) "DATA GUIDE"
5 INTO dg
6 FROM HOTEL_RESERVATIONS;
7 DBMS_JSON.create_view('HOTEL_VIEW','HOTEL_RESERVATIONS','DATA',dg);
8 END;
9* /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from hotel_view;
CREATED_ON ID LAST_MODIFIED VERSION _id room_id hotel_id last_name first_name num_adults total_cost checkin_date num_children checkout_date payment_status reservation_id email phone city country type details quantity
__________________________________ _____________________________ __________________________________ ___________________________________ ______ __________ ___________ ____________ _____________ _____________ _____________ _______________ _______________ ________________ _________________ _________________ _____________________________ ___________ ____________ __________ ________________ ____________________________________ ___________
20-DEC-23 02.39.15.505917000 PM 086582FC930AEC6A389818A1D9 20-DEC-23 02.39.15.505917000 PM 7CDDA8ADC0F54FE3BFA2EA9E503A080F 105 123 Rodriguez Maria 2 650 2023-06-03 0 2023-06-07 paid 1 mrodriguez@example.com 777-4231 Paris France
20-DEC-23 02.39.49.390467000 PM 086582FCB50AEC6A389818A1DF 20-DEC-23 02.39.49.390467000 PM 5BBD60B5C24C4F2CBFB5AC7A51C18AA7 315 123 Lee Ethan 1 350 2023-06-15 0 2023-06-17 paid 2 ethan.lee@example.com 123-8106 Madrid Spain
20-DEC-23 02.40.05.271264000 PM 086582FCC50AEC6A389818A1E5 20-DEC-23 02.40.27.885089000 PM 6F2FA1C41E604FB0BFF87D4613282CD3 207 123 Johnson Olivia 2 932 2023-06-25 0 2023-06-30 paid 3 olivia.johnson@example.com 987-1890 Barcelona Spain
20-DEC-23 02.40.05.271264000 PM 086582FCC50AEC6A389818A1EB 20-DEC-23 02.40.05.271264000 PM AC9EDF1CC0694F0ABF6BCB522966DE33 105 123 Patel Liam 2 350 2023-06-07 0 2023-06-17 paid 4 liam.patel@example.com 123-8106 Malaga Spain
20-DEC-23 02.40.05.271264000 PM 086582FCC50AEC6A389818A1F1 20-DEC-23 02.40.05.271264000 PM D138C6FD1D804F52BF425094E3687DFE 101 123 Smith John 2 800 2023-06-01 1 2023-06-05 paid 5 john.smith@example.com 555-1234 Lyon France extra_bed 1
20-DEC-23 02.40.05.271264000 PM 086582FCC50AEC6A389818A1F1 20-DEC-23 02.40.05.271264000 PM D138C6FD1D804F52BF425094E3687DFE 101 123 Smith John 2 800 2023-06-01 1 2023-06-05 paid 5 john.smith@example.com 555-1234 Lyon France late_checkout Please arrange for a 2pm checkout
6 rows selected.
SQL>
In the last part of the workshop, we are going to learn how to use the MongoDB API against SODA and Duality Views: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-9-mongodb-api-against-duality-views-9a2
Top comments (0)