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 파라미터를 사용하여 주문 배열 내 각 제품의 0부터 시작하는 위치를 표시합니다.

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)