Extracting nested object data

Hi, Im working on a project that uses athena as a datasource, which in turn extracts data from our documentDB collections
On certain documents we have the following structure:
{
someArray: [
{
anotherArray: [
{
data: { someKeyValuePair: String },
},
],
},
];
}

when i try accessing someKeyValuePair via custom sql it doesnt seem to work, the only values i see in the anotherArray are the mongodb _id on each internal object, while someArray holds every value i need from each of its objects
for some context, someArray ALWAYS holds 1 object, anotherArray could hold between 0-2, i tried manipulating the filter and extracts to make sure i never try to access a property of a null object, and still the only value i got was the _id

if you need more context please let me know.
Has anyone encountered this issue? Thanks in advance, Gal.

Hi @galkubani - If you are using SPICE the parseJson function will work

parseJson(json, "$.someArray[0].anotherArray[0].data.someKeyValuePair")

as a dataset calculated field not at analysis runtime (see the public doc for more info)

I didn’t check with a direct connection to Athena , which is not listed as as support db engine in the public doc. You can try and see, there is a small chance it supported but not documented.