Analyzing the guarantee of array preservation in SQL query results

DevOps Google Cloud
1

Hi all,
I have a specific question regarding array unpacking for quite a crucial use case. I have a query like the following:


  ARRAY(
    SELECT distinct field1 FROM UNNEST(array_col) as field1
    --ORDER BY OFFSET
    LIMIT 100
  ) AS distinct_array
FROM
  table1
;```


So array_col is ordered when coming in as an input in the order I want. I basically want to run dedup and limit 100.

So far the order seems preserved, but I would need to know if that is guaranteed or not as in the ARRAY official documentation it is specifically written that the order might be affected (Again in practice the order seems to be maintained)
2

This is from the documentation, so I’d read that as order preservation is not guaranteed:

3

You could first expand the table like this and then put back together again - will guarantee initial order:
with expanded as (
SELECT col1, field1, arr_off
FROM
dev.array_test_table1,
UNNEST(array_col) as field1
WITH OFFSET as arr_off)
select col1, field1, arr_off, row_number() over(partition by col1, field1) as row_num
from expanded