Using array and nested data

[netta]

Hello Helical Team,

I am using helical insight EE version 5.0. We are connecting to MongoDB using Trino. We want to use nested data inside an array but it is not working. For example, for a key which is an array with two documents, I want to either iterate over the array and access the keys or at the very least provide the index for the object to be accessed.

I tried doing different combinations of array[0].key to get the key in the graph but getting error:
javax.sql.rowset.serial.SerialArray@fd5177ed

Thank You,
Netta.

[helicalforum]

Hello Netta,

Use something like below in custom column. This is column name followed by number 1 2 etc which is index number. Even DBfunctions can be used or something like this can also be created even at the metadata view level also.

In custom column write : escalation_detail[1]

Further the JSON which you get you can get further using dot notation

escalation_detail[2].date

Thank You,
Helical Insight.

[shikhapatel]

i am getting the attached error when using either or the query.
citationdata.payment_data[1]
or
“citationdata”.“payment_data”[1]

[helicalforum]

Hello Shikha,

Out of these 2 → citationdata.payment_data[1] or “citationdata”.“payment_data”[1]. Correct syntax is citationdata.payment_data[1]. Also you are getting error because you have data in payment_data where keys are not uniform. Some of the records might be having data in payment_data and some doesn’t have any data entry. In this scenarios where data length inside array is not uniform then it gives this error.

If you initialize the key (even if it is empty) and all your data records have same structure then this will work fine.

Thank you,
Helical Insight.