U0100036: 目标数据库不支持JSON_TABLE。

描述

GaussDB不支持JSON_TABLE，json数据嵌套超过2次的查询手工修改。

数据库类型与版本

• 源库类型与版本：UGO 支持的 Oracle 版本。
• 目标库类型与版本：UGO 支持的 GaussDB 版本。

语法示例

select * from JSON_TABLE(
    '{
    "order": {
        "validDate": "2024-01-01",
        "custId": 10001,
        "offerId": 5001,
        "expireDate": "2024-12-31",
        "ordOfferList": [
            {
                "offerName": "5G畅享套餐",
                "offerType": "主套餐",
                "accessNum": "13800138000",
                "validDate": "2024-01-01",
                "custId": 10001,
                "offerId": 5001,
                "expireDate": "2024-12-31",
                "offerProdRelList": [
                    {"prodId": "PROD001"},
                    {"prodId": "PROD002"}
                ],
                "priceList": [
                    {
                        "priceId": "PRICE001",
                        "chaList": [
                            {
                                "ELEMENT_ID": "E001",
                                "chaSpecId": 1001,
                                "chaValue": "100M",
                                "chaSpecCode": "SPEED"
                            },
                            {
                                "ELEMENT_ID": "E002",
                                "chaSpecId": 1002,
                                "chaValue": "1000分钟",
                                "chaSpecCode": "DURATION"
                            }
                        ]
                    },
                    {
                        "priceId": "PRICE002",
                        "chaList": [
                            {
                                "ELEMENT_ID": "E003",
                                "chaSpecId": 1003,
                                "chaValue": "20G",
                                "chaSpecCode": "FLOW"
                            }
                        ]
                    }
                ]
            },
            {
                "offerName": "宽带套餐",
                "offerType": "附加套餐",
                "accessNum": "13800138001",
                "validDate": "2024-02-01",
                "custId": 10001,
                "offerId": 5002,
                "expireDate": "2024-12-31",
                "offerProdRelList": [
                    {"prodId": "PROD003"}
                ],
                "priceList": [
                    {
                        "priceId": "PRICE003",
                        "chaList": [
                           
                        ]
                    }
                ]
            }
        ]
    }
}',
    '$.order' 
    COLUMNS(
        NESTED PATH '$.ordOfferList[*]'
        COLUMNS(
            offerName VARCHAR2(100) PATH '$.offerName', 
            offerType VARCHAR2(100) PATH '$.offerType',
            accessNum VARCHAR2(100) PATH '$.accessNum',
            NESTED PATH '$.offerProdRelList[*]'  ---嵌套1
            COLUMNS(prodId VARCHAR2(100) PATH '$.prodId'),
            NESTED PATH '$.priceList[*]'   --嵌套2
            COLUMNS(
                priceId VARCHAR2(100) PATH '$.priceId',
                NESTED PATH '$.chaList[*]'  ---嵌套3
                COLUMNS( 
                    ELEMENT_ID VARCHAR2(100) PATH '$.ELEMENT_ID',
                    chaSpecId NUMBER(16) PATH '$.chaSpecId',
                    chaValue VARCHAR2(100) PATH '$.chaValue',
                    chaSpecCode VARCHAR2(100) PATH '$.chaSpecCode'
                )
            ),
            VALIDDATE VARCHAR2(100) PATH '$.validDate',
            CUSTID NUMBER(16) PATH '$.custId',
            OFFERID NUMBER(16) PATH '$.offerId',
            EXPIREDATE VARCHAR2(100) PATH '$.expireDate'
        )
    )
    );
    

改造建议

转换核心：将Oracle的JSON_TABLE函数转换为GaussDB的CTE（公共表表达式）+ JSON函数组合。

转换公式：

• JSON_TABLE → WITH cte AS (json_extract + json_array_elements) SELECT json_object_field_text
• FOR ORDINALITY → row_number() OVER ()
• NESTED PATH → 多层CTE展开嵌套数组
• 空数组 → UNION ALL + json_array_length判断

支持范围：

• 非嵌套：单层CTE
• 嵌套1次：两层CTE + UNION ALL处理空数组
• 嵌套2次：三层CTE + PARTITION BY序号
• 嵌套>2次：报错 U0000036

GaussDB修改后：

with  offer_list AS (
    SELECT 
        json_object_field_text(offer_elem, 'offerName') AS offerName,
        json_object_field_text(offer_elem, 'offerType') AS offerType,
        json_object_field_text(offer_elem, 'accessNum') AS accessNum,
        json_object_field_text(offer_elem, 'validDate') AS validDate,
        json_object_field_text(offer_elem, 'custId') AS custId,
        json_object_field_text(offer_elem, 'offerId') AS offerId,
        json_object_field_text(offer_elem, 'expireDate') AS expireDate,
        COALESCE(json_extract(offer_elem, '$.offerProdRelList'), '[]'::json) AS prod_rel_array,
        COALESCE(json_extract(offer_elem, '$.priceList'), '[]'::json) AS price_array,
        row_number() OVER (ORDER BY json_object_field_text(offer_elem, 'offerName')) AS offer_seq
    FROM json_array_elements(
        json_extract('{
    "order": {
        "validDate": "2024-01-01",
        "custId": 10001,
        "offerId": 5001,
        "expireDate": "2024-12-31",
        "ordOfferList": [
            {
                "offerName": "5G畅享套餐",
                "offerType": "主套餐",
                "accessNum": "13800138000",
                "validDate": "2024-01-01",
                "custId": 10001,
                "offerId": 5001,
                "expireDate": "2024-12-31",
                "offerProdRelList": [
                    {"prodId": "PROD001"},
                    {"prodId": "PROD002"}
                ],
                "priceList": [
                    {
                        "priceId": "PRICE001",
                        "chaList": [
                            {
                                "ELEMENT_ID": "E001",
                                "chaSpecId": 1001,
                                "chaValue": "100M",
                                "chaSpecCode": "SPEED"
                            },
                            {
                                "ELEMENT_ID": "E002",
                                "chaSpecId": 1002,
                                "chaValue": "1000分钟",
                                "chaSpecCode": "DURATION"
                            }
                        ]
                    },
                    {
                        "priceId": "PRICE002",
                        "chaList": [
                            {
                                "ELEMENT_ID": "E003",
                                "chaSpecId": 1003,
                                "chaValue": "20G",
                                "chaSpecCode": "FLOW"
                            }
                        ]
                    }
                ]
            },
            {
                "offerName": "宽带套餐",
                "offerType": "附加套餐",
                "accessNum": "13800138001",
                "validDate": "2024-02-01",
                "custId": 10001,
                "offerId": 5002,
                "expireDate": "2024-12-31",
                "offerProdRelList": [
                    {"prodId": "PROD003"}
                ],
                "priceList": [
                    {
                        "priceId": "PRICE003",
                        "chaList": [
                            
                        ]
                    }
                ]
            }
        ]
    }
}', '$.order.ordOfferList[*]')
    ) AS offer_elem
),
prod_expanded AS (
    SELECT 
        offer_seq,
        offerName,
        offerType,
        accessNum,
        validDate,
        custId,
        offerId,
        expireDate,
        price_array,
        json_array_elements(prod_rel_array) AS prod_rel_elem
    FROM offer_list
    WHERE json_array_length(prod_rel_array) > 0
    
    UNION ALL
    SELECT 
        offer_seq,
        offerName,
        offerType,
        accessNum,
        validDate,
        custId,
        offerId,
        expireDate,
        price_array,
        NULL::json AS prod_rel_elem
    FROM offer_list
    WHERE json_array_length(prod_rel_array) = 0
),
prod_extracted AS (
    SELECT
        offer_seq,
        offerName,
        offerType,
        accessNum,
        validDate,
        custId,
        offerId,
        expireDate,
        price_array,
        json_object_field_text(prod_rel_elem, 'prodId') AS prodId
    FROM prod_expanded
),
price_expanded AS (
    SELECT 
        offer_seq,
        offerName,
        offerType,
        accessNum,
        validDate,
        custId,
        offerId,
        expireDate,
        prodId,
        NULL::json AS price_elem
    FROM prod_extracted
    WHERE prodId IS NOT NULL
    
    UNION ALL
    SELECT 
        p.offer_seq,
        p.offerName,
        p.offerType,
        p.accessNum,
        p.validDate,
        p.custId,
        p.offerId,
        p.expireDate,
        NULL::text AS prodId,
        json_array_elements(p.price_array) AS price_elem
    FROM (SELECT DISTINCT ON (offer_seq) * FROM prod_extracted) p
    WHERE json_array_length(p.price_array) > 0
    
    UNION ALL
    
    SELECT 
        o.offer_seq,
        o.offerName,
        o.offerType,
        o.accessNum,
        o.validDate,
        o.custId,
        o.offerId,
        o.expireDate,
        NULL::text AS prodId,
        NULL::json AS price_elem
    FROM offer_list o
    WHERE json_array_length(o.prod_rel_array) = 0 
      AND json_array_length(o.price_array) = 0
),
price_extracted AS (
    SELECT 
        offer_seq,
        offerName,
        offerType,
        accessNum,
        validDate,
        custId,
        offerId,
        expireDate,
        prodId,
        json_object_field_text(price_elem, 'priceId') AS priceId,
        COALESCE(json_extract(price_elem, '$.chaList'), '[]'::json) AS cha_array  -- 去掉 [*]
    FROM price_expanded
),
cha_expanded AS (
    SELECT 
        offer_seq,
        offerName,
        offerType,
        accessNum,
        validDate,
        custId,
        offerId,
        expireDate,
        prodId,
        priceId,
        json_array_elements(cha_array) AS cha_elem
    FROM price_extracted
    WHERE json_array_length(cha_array) > 0
),
final_result_list AS (
    SELECT 
        offer_seq,
        offerName,
        offerType,
        accessNum,
        validDate,
        custId,
        offerId,
        expireDate,
        prodId,
        priceId,
        cha_elem
    FROM cha_expanded
    
    UNION ALL
    
    SELECT 
        pe.offer_seq,
        pe.offerName,
        pe.offerType,
        pe.accessNum,
        pe.validDate,
        pe.custId,
        pe.offerId,
        pe.expireDate,
        pe.prodId,
        pe.priceId,
        NULL::json AS cha_elem
    FROM price_extracted pe
    WHERE json_array_length(pe.cha_array) = 0
)
-- 最终查询
SELECT 
    offerName,
    offerType,
    accessNum,
    validDate,
    custId,
    offerId,
    expireDate,
    prodId,
    priceId,
    json_object_field_text(cha_elem, 'ELEMENT_ID') AS ELEMENT_ID,
    json_object_field_text(cha_elem, 'chaSpecId') AS chaSpecId,
    json_object_field_text(cha_elem, 'chaValue') AS chaValue,
    json_object_field_text(cha_elem, 'chaSpecCode') AS chaSpecCode
FROM final_result_list;