JSONコレクション表に対するSELECT問合せ

SQL式を使用して、JSONコレクション表のデータを問い合せることができます。SQL問合せは、固定スキーマに基づく表でも同様に機能します。

JSONパス式を指定することで、JSONコレクション表のドキュメント名/値のペアにアクセスできます。ドキュメントの最上位属性には、そのフィールド名をパス式として使用してアクセスでき、ネストされた属性には属性へのパスを使用してアクセスする必要があります。

例を理解するには、ショッピング・アプリケーションのJSONコレクション表を作成し、問合せを実行するサンプル・データ項の説明に従ってサンプル・データ・レコードを挿入します。表のいくつかのサンプル行は次のとおりです。

{"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}]}

例1: ハンドバッグを購入し、規定配送が2023年10月31日より後の買物客から詳細をフェッチします。

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

説明: 10月31日より後に配信される予定のハンドバッグを購入した買物客から詳細をフェッチするには、順序比較演算子anyを使用して、itemおよびEstDeliveryフィールドと必要な値を比較します。論理演算子ANDを使用して、両方の条件に一致する行をフェッチします。

ここでは、ISO-8601形式の文字列形式の日付であり、文字列の自然なソート順が適用されるので、タイムスタンプ・データ型にキャストせずにEstDeliveryを比較できます。

出力:

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

例2: カートに財布やハンドバッグの商品を持っているサンノゼからの買物客にプロモーション・メッセージを表示します。

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";

説明: CASE文を使用すると、買物客がカートに商品walletまたはhandbagを持っている場合に、価格の値下げに関するプロモーション・メッセージを買物客に表示できます。このオファーはSan Joseからの買物客のみを対象としているため、WHERE句で市区町村を指定します。

出力:

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