SELECT queries on JSON collection tables

You can use the SQL expressions to query data from the JSON collection tables. The SQL queries work similarly on tables based on a fixed schema.

You can access the document name/value pairs in a JSON collection table by specifying JSON path expressions. A top-level attribute in the document can be accessed using its field name as the path expression, while a nested attribute must be accessed using a path to the attribute.

To follow along with the examples, create a JSON collection table for a shopping application and insert the sample data records as described in the Sample data to run queries section. A few sample rows from the table are as follows:

{"contactPhone":"1517113582","address":{"city":"Houston","number":651,"state":"TX","street":"Tex Ave","zip":95085},"cart":null,"firstName":"Dierdre","lastName":"Amador","orders":[{"EstDelivery":"2023-11-01","item":"handbag","orderID":"201200", "priceperunit":350},{"EstDelivery":"2023-11-01","item":"Lego","orderID":"201201","priceperunit":5500}]}

{"contactPhone":"1917113999","address":{"city":"San Jose","number":501,"state":"San Francisco","street":"Maine","zip":95095},"cart":[{"item":"wallet","priceperunit":950,"quantity":2},{"item":"wall art","priceperunit":9500,"quantity":1}],"firstName":"Sharon","gender":"F","lastName":"Willard","notify":"yes","wishlist":[{"item":"Tshirt","priceperunit":500},{"item":"Jenga","priceperunit":850}]}

Example 1: Fetch the details from shoppers who have purchased a handbag and the stipulated delivery is after October 31st, 2023.

SELECT contactPhone, firstName
FROM storeAcct s 
WHERE s.orders[].item =any "handbag" AND s.orders[].EstDelivery>=any "2023-10-31"

Explanation: To fetch the details from shoppers who have purchased a handbag that is expected to be delivered after October 31st, you compare the item and EstDelivery fields with the required values using the sequence comparison operator any. You use the logical operator AND to fetch the rows that match both conditions.

Here, you can compare the EstDelivery without casting into a timestamp data type as it is a string-formatted date in ISO-8601 format and the natural sorting order of strings applies.

Output:

{
  "contactPhone" : "1517113582",
  "firstName" : "Dierdre"
}

Example 2: Display promotional messages to shoppers from San Jose who have wallet or handbag items in their carts.

SELECT concat("Hi ",s.firstName) AS Message,
    CASE
        WHEN s.cart.item =any "wallet"
        THEN "The prices on Wallets have dropped"
        WHEN s.cart.item =any "handbag"
        THEN "The prices on handbags have dropped"
        ELSE "Exciting offers on wallets and handbags"
    END AS Offer
FROM storeAcct s

WHERE s.address.city =any "San Jose";

Explanation: You can use CASE statement to display a promotional message to the shoppers regarding the reduction in the prices if the shoppers have the items wallet or handbag in their cart. As the offers are only for shoppers from San Jose, you specify the city in the WHERE clause.

Output:

{"Message":"Hi Sharon","Offer":"The prices on Wallets have
    dropped"}