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.
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.
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
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!
try
select sublist(CONVERT_FROM(‘[1,2,3]’,‘json’ ),-1,1)[0]
make sure the field is array type
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!
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());
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.