Python Developer Track for Oracle JSON and Duality Views - Part 8: Run SQL over JSON
Tags: python, programmers, beginners, tutorial
Published: 2023-12-21 16:48:53

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