We have the below JSON in an Oracle 19C table. The JSON got spaces inserted for the costAllocationID element values.

Example:

"costAllocationID": "1604 "
"costAllocationID": "1556 "
"costAllocationID": "2345 "
"costAllocationID": "1786 "

{
    "artstudents": [
        {
            "resultCode": {
                "code": "12TH"
            },
            "allocations": [
                {
                    "costAllocationID": "1604 ",
                    "allocationAmount": {
                        "amount": 188.29,
                        "currencyCode": "USD"
                    }
                }
            ]
        },
        {
            "resultCode": {
                "code": "11TH"
            },
            "allocations": [
                {
                    "costAllocationID": "1556 ",
                    "allocationAmount": {
                        "amount": 183.66,
                        "currencyCode": "USD"
                    }
                }
            ]
        }
    ],
    "sciencestudents": [
        {
            "resultCode": {
                "code": "12TH"
            },
            "allocations": [
                {
                    "costAllocationID": "2345 ",
                    "allocationAmount": {
                        "amount": 3.22,
                        "currencyCode": "USD"
                    }
                },
                {
                    "costAllocationID": "1786 ",
                    "allocationAmount": {
                        "amount": 3.31,
                        "currencyCode": "USD"
                    }
                }
            ]
        }
        ]
}

This is actually quite a big JSON and we have to trim the spaces for the values in the above elements and change nothing else.

Expected JSON has to look like this:

{
    "artstudents": [
        {
            "resultCode": {
                "code": "12TH"
            },
            "allocations": [
                {
                    "costAllocationID": "1604",
                    "allocationAmount": {
                        "amount": 188.29,
                        "currencyCode": "USD"
                    }
                }
            ]
        },
        {
            "resultCode": {
                "code": "11TH"
            },
            "allocations": [
                {
                    "costAllocationID": "1556",
                    "allocationAmount": {
                        "amount": 183.66,
                        "currencyCode": "USD"
                    }
                }
            ]
        }
    ],
    "sciencestudents": [
        {
            "resultCode": {
                "code": "12TH"
            },
            "allocations": [
                {
                    "costAllocationID": "2345",
                    "allocationAmount": {
                        "amount": 3.22,
                        "currencyCode": "USD"
                    }
                },
                {
                    "costAllocationID": "1786",
                    "allocationAmount": {
                        "amount": 3.31,
                        "currencyCode": "USD"
                    }
                }
            ]
        }
        ]
}

Since this has nested arrays and we are using Oracle 19C, I'm trying to figure out a way to do this with any of the JSON functions available.

I'm trying with JSON_TRANSFORM but it doesn't seem to work:

select  json_transform(STUDENTS_DETAIL, '$.*[*]'
                 (NESTED PATH '$.allocations[*]' 
                         (
                         set '@.costAllocationID'= PATH trim('@.costAllocationID')
                         ))
                                      from ALL_STUDENTS_DETAIL;

Please suggest the best solution for this issue.