Previous Next JavaScript must be enabled to correctly display this content

## 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"}