Query to retrieve and flatten array in results

@vsr1

I have written this query to get data based on requirement.

SELECT id, history FROM pure-ecs-cms d WHERE d.isStacked=true and d.type=“stock” and ARRAY_LENGTH(d.history) > 0

result -
[
{
“history”: [
“22081725-e36e-4c91-82d3-93e4857e14dc”
],
“id”: “0e4ac5dd-24d1-438a-b1dc-5cdeb1ddb1a2”
},
{
“history”: [
“1f9b62f0-bff8-4595-8cd6-659bdbc283d8”,
“923561df-3129-4f8d-a7c5-ba29c81669fb”
],
“id”: “45ef0fdd-a3de-4984-ba29-0c4f777fc0b1”
},
{
“history”: [
“c98826ae-0b5c-4323-af41-c51f4dac0c60”
],
“id”: “d2d09f0e-1d68-414f-b2c6-4c5669324a6d”
},

But I am trying to get result as
[{id1, history1}, {id1, history2}, {id2,history3}, {id3, history4}]

I want id and history as 1 object so that I can loop through each history id.
thank you!

SELECT d.id, h AS historyid
FROM `pure-ecs-cms` d 
UNNEST d.history AS h
WHERE d.isStacked=true and d.type=“stock” and ARRAY_LENGTH(d.history) > 0

I got only ids

[
{
“id”: “0e4ac5dd-24d1-438a-b1dc-5cdeb1ddb1a2”
},
{
“id”: “45ef0fdd-a3de-4984-ba29-0c4f777fc0b1”
},
{
“id”: “45ef0fdd-a3de-4984-ba29-0c4f777fc0b1”
},

how can I get
[{id,historyid}, {id,history}…]

    SELECT d.id, h AS historyid
    FROM `pure-ecs-cms` d 
    UNNEST d.history AS h
    WHERE d.isStacked=true and d.type=“stock” and ARRAY_LENGTH(d.history) > 0
1 Like

Thank you @vsr1 !
Marked this as solution!