admin管理员组

文章数量:1314308

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.

本文标签： oracle19cTrim spaces in the value of a JSON element in a nested JSON array in Oracle 19CStack Overflow

版权声明：本文标题：oracle19c - Trim spaces in the value of a JSON element in a nested JSON array in Oracle 19C - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741963009a2407391.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。