Oracle 19 JSON嵌套子对象查询问题：无法提取receipt_url

问题描述 #

从Stripe API返回的JSON结构中提取数据时，第一层data[*]下的amount、currency字段可以正常获取，但使用NESTED PATH访问data.charges.data下的receipt_url时，结果行数正确但receipt_url字段为空。尝试过多种路径语法（如'$'、'$.[*]'、直接子对象路径等）均无效。

原查询语句：

select 
    AMOUNT / 100, 
    CURRENCY, 
    receipt_url
from json_table (my_api_call,
                '$.data[*]'
                COLUMNS
                  (amount PATH '$.amount',
                  currency PATH '$.currency',
                  NESTED PATH '$.charges.data[*]' columns(receipt_url PATH '$.receipt_url') 
                  )); 

API返回的核心JSON结构：

{
  "data": [
    {
      "amount": 76000,
      "currency": "chf",
      "charges": {
        "data": [
          {
            "receipt_url": "https://pay.stripe.com/receipts/payment/CAcaFwoVYWNjdF8xS09MRjhDVWROTjBoTTZVKNa2saMGMgdkFAIq4_8qCg6IPyLUCEUZDp"
          }
        ]
      }
    },
    {
      "amount": 96000,
      "currency": "chf",
      "charges": {
        "data": [
          {
            "receipt_url": "https://pay.stripe.com/receipts/payment/CAcaFwoVYWNjdFXw6LBYe6v3ziFv1Ty-9gZJbRiaQqWI_sM9YRBojh2aMuHmG6F3"
          }
        ]
      }
    }
  ]
}

解决方案 #

方案1：明确指定字段数据类型 #

空值多数是因为未指定receipt_url的字符串类型，导致数据库无法正确解析URL字符串。修正后的查询语句：

select 
    amount / 100, 
    currency, 
    receipt_url
from json_table (my_api_call,
                '$.data[*]'
                COLUMNS
                  (amount NUMBER PATH '$.amount',
                  currency VARCHAR2(10) PATH '$.currency',
                  NESTED PATH '$.charges.data[*]' columns(receipt_url VARCHAR2(500) PATH '$.receipt_url') 
                  )); 

方案2：直接提取单个Charge的receipt_url #

由于每个Payment Intent通常仅对应一个Charge，可直接提取charges.data数组的第一个元素，无需嵌套展开，写法更简洁：

select 
    amount / 100, 
    currency, 
    JSON_VALUE(charge_data, '$.receipt_url') as receipt_url
from json_table (my_api_call,
                '$.data[*]'
                COLUMNS
                  (amount NUMBER PATH '$.amount',
                  currency VARCHAR2(10) PATH '$.currency',
                  charge_data PATH '$.charges.data[0]'
                  )); 

原因说明 #

1. 字段类型缺失：若未指定receipt_url的字符串类型，部分数据库（如Oracle）会默认按数值类型解析，导致URL字符串无法被识别，返回空值。
2. 路径上下文验证：原NESTED PATH的路径$.charges.data[*]本身正确，但需确保子列路径$.receipt_url是相对于当前嵌套层级的Charge对象，指定类型后可避免解析逻辑错误。

内容的提问来源于stack exchange，提问作者Robinb