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"
}
]
},
}
]
},
}
}
}