Extracting nested object data

Question & Answer
,
1

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.

2

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)

image
image1106×257 14.2 KB

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.

1 Like
3

Hi @galkubani,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

4

Hi @galkubani,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!