How to give an alias to an unnested array of tuples?

user_4375983 0
Viewed 394

I'm trying to set up a set of data using this query :

WITH CTE AS (
SELECT *
FROM UNNEST([("a",NULL),("b",NULL),("c",1),("d",1),("e",2),("f",3),("g",4),("h",4),("i",4),("j",4),("k",5),("l",5),("m",6),("n",7),("o",7)]) 
)
SELECT *
FROM CTE

The yielded result is :

|Row|f0_|f1_ |
|---|---|----|
| 1 | a |null|
| 2 | b |null|
| 3 | c |1   |
| 4 | d |1   |
| 5 | e |2   |
| 6 | f |3   |
| 7 | g |4   |
| 8 | h |4   |
| 9 | i |4   |
| 10| j |4   |
| 11| k |5   |
| 12| l |5   |
| 13| m |6   |
| 14| n |7   |
| 15| o |7   |

What I want is :

|Row| x | y  |
|---|---|----|
| 1 | a |null|
| 2 | b |null|
| 3 | c |1   |
| 4 | d |1   |
| 5 | e |2   |
| 6 | f |3   |
| 7 | g |4   |
| 8 | h |4   |
| 9 | i |4   |
| 10| j |4   |
| 11| k |5   |
| 12| l |5   |
| 13| m |6   |
| 14| n |7   |
| 15| o |7   |
google-bigquery sql
2 Answers

Use STRUCT:

WITH CTE AS (
SELECT *
FROM UNNEST([STRUCT("a" as x,NULL as y),("b",NULL),("c",1),("d",1),("e",2),("f",3),("g",4),("h",4),("i",4),("j",4),("k",5),("l",5),("m",6),("n",7),("o",7)]) 
)
SELECT *
FROM CTE

enter image description here

user_134735250

You can use this trick with almost any SQL dialect:

WITH CTE AS (
  SELECT NULL AS A, NULL AS B
  FROM (SELECT 1) T
  WHERE FALSE
  UNION ALL
  SELECT *
  FROM UNNEST([
    ("a",NULL),("b",NULL),("c",1),("d",1),("e",2),("f",3),
    ("g",4),("h",4),("i",4),("j",4),("k",5),("l",5),("m",6),("n",7),("o",7)
  ]) 
)
SELECT *
FROM CTE;
user_5217990
Related