Query to retrieve and flatten array in results

SQL++
1

@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!

2 
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
3

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}…]

4 
    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
5

Thank you @vsr1 !
Marked this as solution!

6

@vsr1 I am trying this solution in the below query and getting error.

SELECT d.id,
h AS historyid
FROM pure-ecs-cms d
UNNEST d.history AS h
WHERE type=“page”
AND ARRAY_LENGTH(d.history) > 0
AND (d.isStack IS MISSING OR d.isStack <> TRUE)
AND (d.isschedul IS MISSING OR d.isschedul <> TRUE)
AND (d.sup=0 OR d.sup IS MISSING)
AND d.id NOT IN ( SELECT DISTINCT RAW k.iref FROM pure-ecs-history k WHERE k.type = “history”)

7

@PM789 ,

what is error?

WITH ahistory AS (SELECT DISTINCT RAW k.iref FROM `pure-ecs-history` k WHERE k.type = "history" AND k.iref IS NOT NULL)
SELECT d.id, h AS historyid
FROM `pure-ecs-cms` d
UNNEST d.history AS h
WHERE d.type = "page" AND ARRAY_LENGTH(d.history) > 0
      AND IFMISSING(d.isStack, false) != true
      AND IFMISSING(d.isschedul, false) != true
      AND IFMISSING(d.sup, 0) = 0
      AND d.id NOT IN ahistory;
8

This is what I am getting.

[
{
“code”: 3000,
“msg”: “Ambiguous reference to field type.”,
“query”: “WITH ahistory AS (SELECT DISTINCT RAW k.iRef FROM pure-ecs-history k WHERE k.type = “history” AND k.iRef IS NOT NULL)\nSELECT d.id,\n h as historyid \nFROM pure-ecs-cms d\nUNNEST d.history as h\nWHERE type=“page”\n AND ARRAY_LENGTH(d.history) > 0\n AND IFMISSING(d.isStack, false) != true\n AND IFMISSING(d.isschedul , false) != true\n AND IFMISSING(d.sup, 0 ) = 0\n AND d.id NOT IN ahistory”
}
]

9

change type to d.type

10

Thank you. It worked.