Oracle JSON_TABLE查询scores_obtained节点数据异常问题求助

云原生阿岚

2026-6-21

Oracle JSON_TABLE处理BLOB中JSON数据的异常排查

问题现象

首次处理Oracle数据库BLOB列中的JSON数据，使用SQL的JSON_TABLE函数时出现以下异常：

$.evaluations[*].evaluation_list[*].evaluation_meta的嵌套路径引用、channel_meta节点下的直接路径引用均能正常返回数据；
scores_obtained节点数据异常：
1. 通过nested path '$.evaluations[*].evaluation_list[*].scores_obtained'引用返回NULL；
2. 直接路径引用（如$.evaluations[*].evaluation_list[*].scores_obtained.final_score[0]）返回错误值，出现数据偏移（例：6/18记录应返回final_score=80、percent_score=94.12，实际返回下一条记录的70和82.35）。

示例数据

JSON结构

{
  "value": {
    "page": 1,
    "size": 56,
    "total_pages": 1,
    "total_size": 56,
    "evaluation_forms": [
      {
        "template": { },
        "evaluations": [
          {
            "channel_meta": {
              "teamName": ["Team1"],
              "audioFileName": ["509782716366"],
              "inQueueSeconds": ["86.824"]
            },
            "evaluation_list": [
              {
                "evaluation_meta": {
                  "id": "6671a10f965337086e7829e8",
                  "evaluator_name": "Evaluator1",
                  "agent_name": "Agent1",
                  "created_at": "2024-06-18T15:07:50.435Z",
                  "modified_at": "2024-06-18T15:07:50.435Z",
                  "status": "SUBMITTED"
                },
                "response": { },
                "scores_obtained": {
                  "final_score": 80.0,
                  "total_points": 85.0,
                  "percent_score": 94.12,
                  "grade_assigned": "Meets Expectations"
                }
              }
            ]
          },
          {
            "channel_meta": {
              "teamName": ["Team1"],
              "audioFileName": ["508581330938"],
              "inQueueSeconds": ["4.404"]
            },
            "evaluation_list": [
              {
                "evaluation_meta": {
                  "id": "6650b61ebdd8b70c0af26db4",
                  "evaluator_name": "Evaluator2",
                  "agent_name": "Agent2",
                  "created_at": "2024-05-24T15:54:41.468Z",
                  "modified_at": "2024-05-24T15:54:41.468Z",
                  "status": "SUBMITTED"
                },
                "response": { },
                "scores_obtained": {
                  "final_score": 70.0,
                  "total_points": 85.0,
                  "percent_score": 82.35,
                  "grade_assigned": "Needs Work"
                }
              }
            ]
          },
          {
            "channel_meta": {
              "teamName": ["Team1"],
              "audioFileName": ["508887908641"],
              "inQueueSeconds": ["9.133"]
            },
            "evaluation_list": [
              {
                "evaluation_meta": {
                  "id": "6658da061892a3009f5164b9",
                  "evaluator_name": "Evaluator2",
                  "agent_name": "Agent3",
                  "created_at": "2024-05-30T19:58:17.724Z",
                  "modified_at": "2024-05-30T19:58:17.724Z",
                  "status": "SUBMITTED"
                },
                "response": { },
                "scores_obtained": {
                  "final_score": 80.0,
                  "total_points": 85.0,
                  "percent_score": 94.12,
                  "grade_assigned": "Meets Expectations"
                }
              }
            ]
          }
        ]
      }
    ]
  }
}

异常SQL语句

select * from (
select  
    rank () over(partition by evaluation_id order by date_loaded desc) eval_rank,
    b.evaluation_created_at,
    b.evaluation_modified_at,
    
    --debugging
    b.final_score             final_score_nested,
    b.final_score_path        final_score_path,
    b.percent_score,
    
    b.evaluation_id,
    b.evaluator_name,
    b.agent_name,
    b.evaluation_status,        
    b.teamName,
    b.audioFileName,
    b.inQueueSeconds   
from SCHEMAXYZ.TABLE_WITH_BLOB_COLUMN a
    join json_table(a.evaluation_json, '$.value.evaluation_forms[*]'  
        ERROR ON ERROR
        columns (
             template_id path '$.template.id',
             nested path '$.evaluations[*].evaluation_list[*].evaluation_meta' columns (
                 evaluation_id              path '$.id', 
                 evaluator_name,
                 evaluation_agent_id        path '$.agent_id', 
                 agent_name,
                 evaluation_created_at      path '$.created_at',
                 evaluation_modified_at     path '$.modified_at',
                 evaluation_status          path '$.status'
             ),
             ----------------------------------------------------------------------------
             --NOT WORKING (returns NULL)
             nested path '$.evaluations[*].evaluation_list[*].scores_obtained' columns (
                final_score                 
             ),
             --NOT WORKING (returns next-level record somehow ex. 6/18 "70" instead of "80" for final score)
             final_score_path               path '$.evaluations[*].evaluation_list[*].scores_obtained.final_score[0]',
             percent_score                 path '$.evaluations[*].evaluation_list[*].scores_obtained.percent_score[0]',
             ----------------------------------------------------------------------------
             teamName                       path '$.evaluations[*].channel_meta.teamName[0]',
             audioFileName                  path '$.evaluations[*].channel_meta.audioFileName[0]',
             inQueueSeconds                 path '$.evaluations[*].channel_meta.inQueueSeconds[0]'
        )
    ) b on 1=1 
) mr 
WHERE EVALUATION_ID IS NOT NULL
    AND MR.EVALUATION_STATUS NOT IN ('DELETED','NA')
    AND MR.EVAL_RANK = 1
    AND evaluation_id IN (
        '6671a10f965337086e7829e8', --6/18
        '6650b61ebdd8b70c0af26db4', --5/24 
        '6658da061892a3009f5164b9' --5/30
    )
ORDER BY evaluation_created_at DESC

问题原因

嵌套路径返回NULL：当前SQL中scores_obtained的嵌套路径是独立于evaluation_meta的，Oracle JSON_TABLE中多个无关联的nested path会各自展开行，导致scores_obtained的数据无法和对应的evaluation_meta行匹配，最终返回NULL。
直接路径数据偏移：
- $.evaluations[*].evaluation_list[*].scores_obtained.final_score[0]路径会返回所有匹配的final_score值组成的数组，当直接引用时，Oracle会按顺序填充到结果行，和当前行的evaluation_meta失去关联，出现数据偏移；
- final_score本身是单个数值类型，不是数组，添加[0]属于错误的路径写法，会导致解析逻辑异常。

修正方案

采用递进式嵌套，将关联数据放在同一层级的嵌套中，确保数据一一对应：

select * from (
select  
    rank () over(partition by evaluation_id order by date_loaded desc) eval_rank,
    b.evaluation_created_at,
    b.evaluation_modified_at,
    
    -- 修正后的分数字段
    b.final_score,
    b.percent_score,
    
    b.evaluation_id,
    b.evaluator_name,
    b.agent_name,
    b.evaluation_status,        
    b.teamName,
    b.audioFileName,
    b.inQueueSeconds   
from SCHEMAXYZ.TABLE_WITH_BLOB_COLUMN a
    join json_table(a.evaluation_json, '$.value.evaluation_forms[*]'  
        ERROR ON ERROR
        columns (
             template_id path '$.template.id',
             -- 先嵌套evaluations层级，获取channel_meta
             nested path '$.evaluations[*]' columns (
                 teamName path '$.channel_meta.teamName[0]',
                 audioFileName path '$.channel_meta.audioFileName[0]',
                 inQueueSeconds path '$.channel_meta.inQueueSeconds[0]',
                 -- 再嵌套evaluation_list层级，关联evaluation_meta和scores_obtained
                 nested path '$.evaluation_list[*]' columns (
                     -- evaluation_meta字段
                     evaluation_id path '$.evaluation_meta.id', 
                     evaluator_name path '$.evaluation_meta.evaluator_name',
                     agent_name path '$.evaluation_meta.agent_name',
                     evaluation_created_at path '$.evaluation_meta.created_at',
                     evaluation_modified_at path '$.evaluation_meta.modified_at',
                     evaluation_status path '$.evaluation_meta.status',
                     -- scores_obtained字段，和evaluation_meta同层级关联
                     final_score path '$.scores_obtained.final_score',
                     percent_score path '$.scores_obtained.percent_score'
                 )
             )
        )
    ) b on 1=1 
) mr 
WHERE EVALUATION_ID IS NOT NULL
    AND MR.EVALUATION_STATUS NOT IN ('DELETED','NA')
    AND MR.EVAL_RANK = 1
    AND evaluation_id IN (
        '6671a10f965337086e7829e8', --6/18
        '6650b61ebdd8b70c0af26db4', --5/24 
        '6658da061892a3009f5164b9' --5/30
    )
ORDER BY evaluation_created_at DESC

修正说明

层级嵌套改为递进式：先嵌套evaluations[*]获取channel_meta，再在其内部嵌套evaluation_list[*]，将evaluation_meta和scores_obtained放在同一个节点下，确保数据一一对应；
移除final_score[0]中的[0]，匹配JSON中final_score的数值类型；
消除了独立嵌套导致的行不关联问题，确保每个evaluation_id对应正确的分数数据。

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

Oracle JSON_TABLE查询scores_obtained节点数据异常问题求助

问题现象 #

示例数据 #

JSON结构 #

异常SQL语句 #

问题原因 #

修正方案 #

修正说明 #

问题现象

示例数据

JSON结构

异常SQL语句

问题原因

修正方案

修正说明