2

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.

1 Answer 1

1

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;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.