如何使用SQL的JSON_TABLE查询含嵌套嵌套数组的JSON数据？

问题根源 #

你的查询无法返回电话号码，核心问题有三点：

1. 根路径错误：样本JSON没有data顶层节点，原查询$.data[*]无法匹配任何数据
2. contactInfo处理错误：JSON里contactInfo是单个对象而非数组，用$.contactInfo[*]会导致该部分数据为空
3. 字段映射错误：JSON中电话号码的ID字段是phoneID，原查询写的phoneNumberId无法匹配

修正后的查询语句 #

SELECT A.*
FROM
mytable b,
JSON_TABLE
(b.json_data, '$'  -- 若存储的是JSON数组则改用'$[*]'
COLUMNS
(
instId NUMBER(5) PATH '$.instId',
instappId number(15) PATH '$.instappId',
instappNumber VARCHAR2(30) PATH '$.instappNumber',
appDate DATE PATH '$.appDate',
-- 直接映射单个contactInfo对象的字段
firstName VARCHAR2(50) PATH '$.contactInfo.firstName',
middleName VARCHAR2(50) PATH '$.contactInfo.middleName',
lastName VARCHAR2(50) PATH '$.contactInfo.lastName',
emailAddress VARCHAR2(50) PATH '$.contactInfo.emailAddress',
-- 正确嵌套查询contactInfo下的phoneNumbers数组
NESTED PATH  '$.contactInfo.phoneNumbers[*]' COLUMNS (
phoneNumberId NUMBER(15) PATH '$.phoneID',  -- 修正字段名匹配JSON中的phoneID
phoneType VARCHAR2(50) PATH '$.phoneType',
phoneNumber VARCHAR2(50) PATH '$.phoneNumber'),
-- 嵌套查询workHistory数组
NESTED PATH  '$.workHistory[*]' COLUMNS (
companyName VARCHAR2(50) PATH '$.companyName',
startDate DATE PATH '$.startDate',
endDate DATE PATH '$.EndDate'  -- 匹配JSON中大写E的EndDate字段
)
)
)
A;

关键修正说明 #

• 根路径调整：单条JSON对象用$，多条JSON组成的数组用$[*]
• contactInfo处理：直接通过$.contactInfo.xxx映射字段，无需NESTED PATH（它不是数组）
• 字段名匹配：严格对应JSON中的字段名（注意大小写和命名一致性）
• phoneNumbers嵌套：通过$.contactInfo.phoneNumbers[*]准确定位嵌套数组

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