Add/access the index of the array of structs in bigquery

Question

I have a dataset like this

SELECT 'Blue' AS class, [STRUCT('Alice' AS name,18 AS age), STRUCT('Bob' AS name,17 AS age), STRUCT('Charlie' AS name,20 AS age)] as details

I would like to be able to add or access the index of each element of the array like below

SELECT 'Blue' AS class, [STRUCT('Alice' AS name,18 AS age, 1 AS index), STRUCT('Bob' AS name,17 AS age, 2 AS index), STRUCT('Charlie' AS name,20 AS age, 3 AS index)] as details

The ultimate aim is to achieve something like this. So if the index can be accessed on the fly while creating the string_agg, that would be great

with sub as (SELECT 'Blue' AS class, [STRUCT('Alice' AS name,18 AS age, 1 AS index), STRUCT('Bob' AS name,17 AS age, 2 AS index), STRUCT('Charlie' AS name,20 AS age, 3 AS index)] as details
)

select 
class,
STRING_AGG('person_'|| index || '_name =' || name || ', person_'|| index || '_age =' || age) AS output
from sub, unnest(details)
group by class

Thanks

Jaytiger · Accepted Answer · 2022-12-07T11:24:37.807

0

Would you consider below without a temporary structure for a final result?

WITH sample_data AS (
  SELECT 'Blue' AS class, [STRUCT('Alice' AS name,18 AS age), STRUCT('Bob' AS name,17 AS age), STRUCT('Charlie' AS name,20 AS age)] as details
)
SELECT class, STRING_AGG(FORMAT('person_%d_name=%s, person_%d_age=%d', idx + 1, name, idx + 1, age)) AS output
  FROM sample_data, UNNEST(details) WITH OFFSET idx
 GROUP BY 1;

WITH OFFSET clause provides you an index of each element in your array.

For temporary structure, you can consider below.

SELECT class, ARRAY(SELECT AS STRUCT d.*, offset + 1 AS index FROM t.details d WITH OFFSET) details
  FROM sample_data t;

Adding indexes during creation.

WITH sample_table AS (
  select 'Alice' AS ID, 1 as col1, 3 as col2, -2 as col3, 9 as col4
   union all
  select 'Bob' AS ID, -9 as col1, 2 as col2, 5 as col3, -6 as col4
)
SELECT ID, ARRAY_AGG(STRUCT(column, value, index) ORDER BY ABS(value) DESC LIMIT 3) output
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ABS(value) DESC) index 
      FROM sample_table UNPIVOT (value FOR column IN (col1, col2, col3, col4))
  ) 
 GROUP BY ID;

edited Dec 07 '22 at 11:24

answered Dec 07 '22 at 00:58

Jaytiger

11,626
2
5
15

Thank you very much. In order to add the index as part of the creation of the array of structs, it seems like i have to first create the details array of struct then query sample_data without the index, then use offset to get the index after creation. I cannot access index during the creation – jasmine Dec 07 '22 at 10:17
@jasmine, I think you can add the index with a window function like *ROW_NUMBER()* during creation of the array of structs, though I have no idea how you create the array of structs. – Jaytiger Dec 07 '22 at 10:39
Thank you, I tried to access it using a rank, but got errors. I will try to create a more accurate replica of what's actually happening. It's essentially for this question, so in the output add the rank as another column in the struct https://stackoverflow.com/questions/74635659/how-to-get-top-3-columns-and-their-values-across-multiple-columns-dynamically – jasmine Dec 07 '22 at 11:08
@jasmine, would you check the updated answer if it suits your needs ? – Jaytiger Dec 07 '22 at 11:25
1

Thank you, this is actually the solution i was currently using, i think both your answers are extremely useful, so i will accept both thanks! – jasmine Dec 07 '22 at 13:40

Add/access the index of the array of structs in bigquery

1 Answers1

Adding indexes during creation.