Exemplos de UNNEST

UNNEST é um parâmetro na cláusula FROM que expande os dados aninhados em colunas que contêm os elementos não aninhados dos dados. Para ter informações sobre como desagrupar dados, consulte Consultar dados semiestruturados.

A instrução a seguir cria e preenche a tabela orders, que contém uma coluna products com matrizes de IDs de produto. Os exemplos desta seção utilizam os dados de amostra nessa tabela.

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

Veja a seguir alguns exemplos de consulta desaninhada com os dados de amostra usando a sintaxe partiQL.

Desagrupar uma matriz sem uma coluna OFFSET

A consulta a seguir desagrupa as matrizes SUPER na coluna de produtos, e cada linha representa um item do pedido em 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)

A consulta a seguir localiza o produto mais caro em cada pedido.

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)

Desagrupar uma matriz com uma coluna OFFSET implícita

A consulta a seguir usa o parâmetro UNNEST ... WITH OFFSET para mostrar a posição com base em zero de cada produto na respectiva matriz de pedidos.

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)

Como a declaração não especifica um alias para a coluna de deslocamento, o Amazon Redshift a denomina offset_col por padrão.

Desagrupar uma matriz com uma coluna OFFSET explícita

A consulta a seguir também usa o parâmetro UNNEST ... WITH OFFSET para mostrar os produtos nas respectivas matrizes de pedidos. A diferença nessa consulta em comparação com a consulta no exemplo anterior é que ela nomeia explicitamente a coluna de deslocamento com o 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)