1

I have a table with schema as below

fields. TIME_256Hz  TIMESTAMP   REPEATED    
fields. SAC_ACCELX  FLOAT   REPEATED    
fields. SAC_ACCELY  FLOAT   REPEATED    
fields. SAC_ACCELZ  FLOAT   REPEATED    
fields. SAC_GYROX   FLOAT   REPEATED    
fields. SAC_GYROY   FLOAT   REPEATED    
fields. SAC_GYROZ   FLOAT   REPEATED    
fields. SAC_PRESSURE    FLOAT   REPEATED    
fields. TARGET  STRING  REPEATED

The table looks like

enter image description here

I try to sort the data with timestamp using this command

select * from liftpdm.liftpdm_2.acc8 
order by fields.TIME_256Hz desc

BUT

I keep getting the following error:

ORDER BY does not support expressions of type ARRAY<TIMESTAMP> at [2:10]

I tried to change the Datatype to Datetime, string but same error.

Please suggest how to sort out this data

Thanks

user437777
  • 1,423
  • 4
  • 17
  • 28
  • Do you actually *need* to have the fields `REPEATED`? – Nick Dec 26 '19 at 08:09
  • It is the mode. I tried Nullable but that is wrong – user437777 Dec 26 '19 at 08:39
  • I get following error when I change to Nullable { "errors" : [ { "debugInfo" : "", "location" : "fields.time_256hz", "message" : "Array specified for non-repeated field.", "reason" : "invalid" } ], "index" : 0 } – user437777 Dec 26 '19 at 08:51

1 Answers1

2

Reproducible error:

WITH data AS (
  SELECT [1,2] arr UNION ALL
  SELECT [2,1] UNION ALL
  SELECT [4,5,6]
)

SELECT * FROM data
ORDER BY arr

# ORDER BY does not support expressions of type ARRAY<INT64> at [8:10]

First you need to decide what do you really want to ORDER BY, as ordering by an array doesn't make much sense.

Some alternatives:

SELECT * FROM data
ORDER BY arr[SAFE_OFFSET(0)]

enter image description here

SELECT * FROM data
ORDER BY arr[SAFE_OFFSET(1)]

enter image description here

SELECT * FROM data
ORDER BY ARRAY_LENGTH(arr) DESC

enter image description here

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • Thanks, a very important pointer. You are right, why would I need to order by arrays. Actually I need to order the entire column but the column has arrays of 256 records per row. So I need to somehow have the table like this from your example Rows =[1,2,3,4,5,6,7] and arr=[1,2,2,1,4,5,6] then I need to do order by on it. Can you suggest a way of doing it? thanks – user437777 Dec 27 '19 at 01:58
  • Post a new question please! – Felipe Hoffa Dec 27 '19 at 05:01