Contoh UNNEST

UNNEST adalah parameter dalam klausa FROM yang memperluas data bersarang ke dalam kolom yang menyimpan elemen data yang tidak bersarang. Untuk informasi tentang data yang tidak bersarang, lihat. Meminta data semi-terstruktur

Pernyataan berikut membuat dan mengisi orders tabel, yang berisi products kolom yang berisi array produk. IDs Contoh di bagian ini menggunakan data sampel dalam tabel ini.

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"
            }
        }
    ]
'));

Berikut ini adalah beberapa contoh query unnesting dengan data sampel menggunakan sintaks PartiQL.

Melepaskan array tanpa kolom OFFSET

Kueri berikut menghapus array SUPER di kolom produk, dengan setiap baris mewakili item dari urutan dalam. 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)

Kueri berikut menemukan produk paling mahal di setiap pesanan.

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)

Melepaskan array dengan kolom OFFSET implisit

Kueri berikut menggunakan UNNEST ... WITH OFFSET parameter untuk menunjukkan posisi berbasis nol dari setiap produk dalam array urutannya.

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)

Karena pernyataan tidak menentukan alias untuk kolom offset, Amazon Redshift secara default menamainya. offset_col

Melepaskan array dengan kolom OFFSET eksplisit

Kueri berikut juga menggunakan UNNEST ... WITH OFFSET parameter untuk menampilkan produk dalam array pesanan mereka. Perbedaan dalam kueri ini dibandingkan dengan kueri pada contoh sebelumnya adalah bahwa ia secara eksplisit menamai kolom offset dengan alias. 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)