I don't know anything about json so apologies if my terminology is incorrect.
I am trying to extract a list of UUIDs from the following json object PAYLOAD:
{
"AccountId": {
"providerId": "12345",
"provider": "ABCD"
},
"destinationAccountId": null,
"referenceId": "ABCD1234",
"repaymentAmounts": {
"75e7dae4-3000-4b51-a1e2-555218d6c180": {
"amount": 5.20,
"currency": "GBP"
},
"b7b7f744-db7f-48c9-8417-985d6fe137bc": {
"amount": 0.24,
"currency": "GBP"
},
"49c69500-e9c0-433e-bd2e-cb387d1b688f": {
"amount": 7.30,
"currency": "GBP"
}
}
}
All I need is a table containing the UUIDs as follows:
| UUID |
|---|
| 75e7dae4-3000-4b51-a1e2-555218d6c180 |
| b7b7f744-db7f-48c9-8417-985d6fe137bc |
| 49c69500-e9c0-433e-bd2e-cb387d1b688f |
Currently I have got as far as
JSON_EXTRACT_PATH_TEXT(PAYLOAD), 'repaymentAmounts')
The output of this is just the array in json format, not extracted:
{
"75e7dae4-3000-4b51-a1e2-555218d6c180": {
"amount": 5.20,
"currency": "GBP"
},
"b7b7f744-db7f-48c9-8417-985d6fe137bc": {
"amount": 0.24,
"currency": "GBP"
},
"49c69500-e9c0-433e-bd2e-cb387d1b688f": {
"amount": 7.30,
"currency": "GBP"
}
}
I don't know how to extract the UUIDs as they do not have a key that I can reference. I'm not familiar enough with flatten to be able to use it for this although I suspect that will be part of the solution.