Querying fields from ARRAY(ROW(ARRAY(ROW()))) recursive nested array of rows in elasticsearch via trino

Trino
1

Hello Team,

We are querying fields from ARRAY(ROW(ARRAY(ROW))), structure is like below :

"dt1" : [
  {
    "id" : "202209",
    "tId" : "2",
    "instances" : [
      {
        "instanceId" : "20220913",
        "instanceVersion" : "4324.543",
        "owners" : [
          {
            "hId" : "10615",
            "hName" : "UI"
          }
        ]
      }
 ]
},
  {
    "id" : "202210",
    "tId" : "3",
    "instances" : [
      {
        "instanceId" : "20220913",
        "instanceVersion" : "4324.550",
        "owners" : [
          {
            "hId" : "10615",
            "hName" : "BI"
          }
        ]
      }
 ]
}
  ]

We are reading data with UNNEST using JSON_EXTRACT function and casting it as ARRAY(ROW(id as VARCHAR, tid as varchar, instances as JSON)), while further reading reading second level of json, we need to give reference of json path in JSON_EXTRACT(main_source,‘$.dt**[0]**.instances’), its not allowing to use wild card to select $.dt[*] in json extract function.

Currently as a workaround we are performing union of queries to select all possible levels of ARRAY(ROW()). Let me know possible way to pass wildcard for selecting all dt[*] or dt[.] in single query