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:

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.

CodePudding user response：

You could do a recursive flatten and then exclude the keys amount and currency since those aren't what you want. This works for the scenario you've requested, but might need tweaking if you have other keys in your actual data:

WITH x AS (
    SELECT parse_json('{
    "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"
        }
    }
}') as var)
SELECT y.key
FROM x,
LATERAL FLATTEN(input=>x.var:repaymentAmounts, recursive=>True) y
WHERE y.key not in ('amount','currency');

You can also skip the recursive query and the filter, now that I look at it closer:

WITH x AS (
    SELECT parse_json('{
    "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"
        }
    }
}') as var)
SELECT y.key
FROM x,
LATERAL FLATTEN(input=>x.var:repaymentAmounts) y;