Presto Json Parsing

Viewed 138

I have a json field(attached sample) and i need to extract the values in ProvisioningSystem path but it works only if i hardcode the array location.How can i extract the value without hardcoding ?Thanks in Advance!

Code:

TRANSFORM(CAST(JSON_EXTRACT(order_json, '$.Order.Accounts.Account') AS ARRAY), x -> JSON_EXTRACT_SCALAR(x,'$.ProvisioningSystems.ProvisioningSystem[1].SystemName'))

Json:

 {
  "Order":
  {
   "Accounts": {
     "Account": [
   {
          "ProvisioningSystems": {},
   },
        {
         "ProvisioningSystems": {
            "ProvisioningSystem": [
              {
                "SystemOrderRef": "12345",
                "SystemName": "Testsystem",
                "SystemOrderRefType": "Provision"
              }
            ]
          },
        }
      ]
    },
    }
  }
}
0 Answers
Related