How to create a 1D array using multiple loop variables

SQL++
,
1

Hi,

Can some of you please tell me how to create a 1D array using multiple variables?

So for example in python, the following code produces a 1d array:
[a + b for a in range(3) for b in range(3)] -> [0, 1, 2, 1, 2, 3, 2, 3, 4]

I am a beginner, but if I run the following query:
SELECT VALUE ARRAY "a=" || TO_STR(a) || " b=" || TO_STR(b) FOR b IN [1,2,3] END FROM [1,2,3] AS a;
It results in

[
  [
    "a=1 b=1",
    "a=1 b=2",
    "a=1 b=3"
  ],
  [
    "a=2 b=1",
    "a=2 b=2",
    "a=2 b=3"
  ],
  [
    "a=3 b=1",
    "a=3 b=2",
    "a=3 b=3"
  ]
]

instead of

[
    "a=1 b=1",
    "a=1 b=2",
    "a=1 b=3",
    "a=2 b=1",
    "a=2 b=2",
    "a=2 b=3",
    "a=3 b=1",
    "a=3 b=2",
    "a=3 b=3"
]

I could not manage to combine multiple ARRAY … END (or subquery) expressions to yield a 1D array.
Sorry for the noob question.
Can someone tell me how to do what I asked above?

Thank you in advance!

2

Hi @mtannerman ,

SELECT VALUE ARRAY_FLATTEN(ARRAY_AGG(ARRAY "a=" || TO_STR(a) || " b=" || TO_STR(b) FOR b IN [1,2,3] END) ,1) FROM [1,2,3] AS a;

SELECT VALUE ARRAY "a=" || TO_STR(IDIV(a,3)) || " b=" || TO_STR(IMOD(a,3)) FOR a IN ARRAY_RANGE(0,9) END;

SELECT VALUE ARRAY_FLATTEN(ARRAY (ARRAY "a=" || TO_STR(a) || " b=" || TO_STR(b) FOR b IN ARRAY_RANGE(0,3) END) FOR a IN ARRAY_RANGE(0,3) END,1);

SELECT VALUE ARRAY_AGG( "a=" || TO_STR(a) || " b=" || TO_STR(b)) FROM [1,2,3] AS a UNNEST [1,2,3] AS b;

SELECT VALUE "a=" || TO_STR(a) || " b=" || TO_STR(b) FROM [1,2,3] AS a UNNEST [1,2,3] AS b;

ARRAY_RANGE(), DATE_RANGE()

1 Like