Last element in array?

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



gives me this error:

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

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

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

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

source code in github

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

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: (12,3,KB)

And here the sample JSON dataset: (634,Bytes)

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

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

make sure the field is array type

Doing like this:

select sublist(CONVERT_FROM('[{ "_id": { "$oid": "6254c1004df7bc001cb83aa2" }, "url": "", "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": "", "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": "", "document_referrer": "", "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?


sublist cannot work for struct

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

source code