CODEWITHSUNDEEP
arraysjsonsortinggoogle-bigquery
franco_b
franco_b

Reputation: 878

Google BigQuery: position of element in array

Is it possible get the position of element in array in Google BigQuery? Something like array_postion function in Postgresql?

In particular I need to get the array position of element after unnest array:

WITH data_table AS
(SELECT '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]}' AS data)

SELECT fruit, ? as position
FROM data_table, UNNEST(JSON_EXTRACT_ARRAY(  data,  '$.fruit')) as fruit

fruit                     |  position
-------------------------------------
{"apples":5,"oranges":10} |  1
{"apples":2,"oranges":4}  |  2

I try a workaound using row_number(), but I'm not sure about sorting

WITH data_table AS
(SELECT '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]}' AS data)

SELECT fruit, ROW_NUMBER() OVER() AS position
FROM data_table, UNNEST(JSON_EXTRACT_ARRAY(  data,  '$.fruit')) as fruit

Upvotes: 1

Views: 2830

Answers (1)

Martin Weitzmann
Martin Weitzmann

Reputation: 4746

That's possible using with offset:

WITH data_table AS
(SELECT '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]}' AS data)

SELECT fruit, i
FROM data_table, UNNEST(JSON_EXTRACT_ARRAY(  data,  '$.fruit')) as fruit with offset as i

Upvotes: 4

Related Questions