Last element in array?

#1

Is it possible to retrieve the last element in an array?
I can do:
datasetpathlist[0]

but

datasetpathlist[array_length(datasetpathlist)]

gives me this error:

org.apache.calcite.rex.RexCall cannot be cast to org.apache.calcite.rex.RexLiteral.

#2

Hi @Joe This has been a request from others too but currently not in the product,

#3

try sublist([array],-1,1)[0]

#4

That worked, thank you! I don’t see sublist in the SQL reference – is there a list of undocumented functions somewhere?

#5

source code in github

#6

Wow - there’s a bunch: Search · implements simplefunction · GitHub

#7

Hi! I tried that… but I get the error:
UnsupportedOperationException: STRUCT

I’m using Dremio CE version 22.0.0-202206221430090603-1fa4049f.

Here is the query profile:
struct_error_profile.zip (12,3,KB)

And here the sample JSON dataset:
sublist_test.json.zip (634,Bytes)

Do you have an idea to get the last element of the array using other method?
Thanks!

#8

try
select sublist(CONVERT_FROM(‘[1,2,3]’,‘json’ ),-1,1)[0]

make sure the field is array type

#9

Hi!
Doing like this:

select sublist(CONVERT_FROM('[{ "_id": { "$oid": "6254c1004df7bc001cb83aa2" }, "url": "https://www.xxxxxxxxxxxxxxxx.com.br/", "title": "Title", "page_type": "product", "products": [ { "_id": { "$oid": "6254c1004df7bc001cb83aa3" }, "product_id": "276", "product_sku": "TTN22793", "product_name": "Xxxxxxxxxxxxxxxxxxxxxxxxx", "product_value": 153.94, "category_code": 26, "category_name": "xxxxxxxxxxxxxxxxxxxx", "quantity": 1 } ], "created_at": { "$date": "2022-04-12T00:00:00.021+0000" } }, { "_id": { "$oid": "6254c14f4df7bc001cb84b17" }, "url": "https://www.xxxxxxxxxxxxxxx.com.br/", "title": "xxxxxxxxxxxxxxxx", "page_type": "product", "products": [ { "_id": { "$oid": "6254c14f4df7bc001cb84b18" }, "product_id": "276", "product_sku": "TTN22793", "product_name": "xxxxxxxxxxxxxxxxxxxxxxxxxx", "product_value": 153.94, "category_code": 26, "category_name": "xxxxxxxxxxxxxxxxxxxxxxxxx", "quantity": 1 } ], "created_at": { "$date": "2022-04-12T00:01:19.850+0000" } }, { "_id": { "$oid": "6254c533c4a761001bd0c170" }, "url": "https://www.xxxxxxxxxxxxxxxxxxx.com.br/xxxxxxxx", "document_referrer": "https://www.xxxxxxxxxxxxxxxxx.com.br/", "title": "xxxxxxxxxxxxxxxxxxxxxxxxxxx", "products": [], "created_at": { "$date": "2022-04-12T00:17:55.929+0000" }}]','json' ),-1,1)[0]

I got the same error.
UnsupportedOperationException: STRUCT

I think It didn’t work if you have objects inside array… like this:

select sublist(CONVERT_FROM('[{"prop": "value"}]','json' ),-1,1)[0]

Also UnsupportedOperationException: STRUCT

And with:

select sublist(CONVERT_FROM('["one","two","three"]','json' ),-1,1)[0]

Works fine.

Do you think I can have some workarounds?

Thanks!

#10

sublist cannot work for struct

    case STRUCT:
    case NULL:
    case UINT1:
    case UINT2:
    case UINT4:
    case UINT8:
    case UNION:
    default:
      throw new UnsupportedOperationException(from.getMinorType().toString());

source code

#11

Hello @flavin !
The Dremio CE v.23.0.1 was launched!
Maybe the new Map type solves the problem I’m having with STRUCT!
I’ll test and post results here.

https://docs.dremio.com/software/release-notes/230-release/