UNNEST の例

UNNEST は FROM 句のパラメータで、ネストされたデータを、データのネストされていない要素を保持する列に展開します。データのネスト解除の詳細については、「半構造化データのクエリ」を参照してください。

次のステートメントは、製品 ID の配列を含む products 列を含む orders テーブルを作成して入力します。このセクションの例では、このテーブルのサンプルデータを使用します。

CREATE TABLE orders (
    order_id INT,
    products SUPER
);

-- Populate table
INSERT INTO orders VALUES
(1001, JSON_PARSE('[
        {
            "product_id": "P456",
            "name": "Monitor",
            "price": 299.99,
            "quantity": 1,
            "specs": {
                "size": "27 inch",
                "resolution": "4K"
            }
        }
    ]
')),
(1002, JSON_PARSE('
    [
        {
            "product_id": "P567",
            "name": "USB Cable",
            "price": 9.99,
            "quantity": 3
        },
        {
            "product_id": "P678",
            "name": "Headphones",
            "price": 159.99,
            "quantity": 1,
            "specs": {
                "type": "Wireless",
                "battery_life": "20 hours"
            }
        }
    ]
'));

以下は、PartiQL 構文を使用してサンプルデータでクエリをネスト解除する例です。

OFFSET 列のない配列のネスト解除

次のクエリは、製品列の SUPER 配列をネスト解除します。各行は order_id の注文品目を表します。

SELECT o.order_id, unnested_products.product
FROM orders o, UNNEST(o.products) AS unnested_products(product);

 order_id |                                                           product                                                           
----------+-----------------------------------------------------------------------------------------------------------------------------
     1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}
     1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3}
     1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}}
(3 rows)

次のクエリでは、各注文で最も高価な製品を見つけます。

SELECT o.order_id, MAX(unnested_products.product)
FROM orders o, UNNEST(o.products) AS unnested_products(product);

 order_id |                                                           product                                                           
----------+-----------------------------------------------------------------------------------------------------------------------------
     1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}
     1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}}
(2 rows)

暗黙的な OFFSET 列を持つ配列のネスト解除

次のクエリでは、UNNEST ... WITH OFFSET パラメータを使用して、注文配列内の各製品のゼロベースの位置を表示します。

SELECT o.order_id, up.product, up.offset_col
FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product);

 order_id |                                                           product                                                           | offset_col 
----------+-----------------------------------------------------------------------------------------------------------------------------+------------
     1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}             |          0
     1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3}                                                          |          0
     1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} |          1
(3 rows)

ステートメントはオフセット列のエイリアスを指定していないため、Amazon Redshift はデフォルトで offset_col という名前を付けます。

明示的な OFFSET 列を持つ配列のネスト解除

次のクエリでも、UNNEST ... WITH OFFSET パラメータを使用して注文配列内の製品を表示します。このクエリが前の例のクエリと違う点は、オフセット列に明示的にエイリアス idx という名前を付けることです。

SELECT o.order_id, up.product, up.idx
FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product, idx);

 order_id |                                                           product                                                           | idx 
----------+-----------------------------------------------------------------------------------------------------------------------------+-----
     1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}             |   0
     1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3}                                                          |   0
     1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} |   1
(3 rows)