如何通过SQL提取JSON内部数组中的多个元素作为结果列值

问题核心原因 #

原有SQL的错误在于没有正确处理responses节点下的动态UUID键，且嵌套路径的上下文定位错误，没有指向最终的XML数组层级。

调整后可用SQL #

直接使用JSON通配符匹配动态UUID键，定位到数组元素层级即可：

SELECT 
    tipo,
    seq,
    response
FROM response r,
     JSON_TABLE(
         r.payload, 
         '$'
         COLUMNS (
             tipo VARCHAR2(64) PATH '$.type',
             NESTED PATH '$.data.responses.*[*]'
             COLUMNS (
                 seq FOR ORDINALITY,
                 response VARCHAR2(4000) PATH '$'
             )
         )
     ) jt;

关键修改说明 #

• 用$.data.responses.*[*]作为嵌套路径：*通配符匹配responses节点下的所有动态键（即示例中的UUID值），直接定位到该键对应数组的所有元素
• 移除了response字段的FORMAT JSON修饰：需要提取的是字符串类型的XML值，不需要JSON格式输出，避免返回结果带多余引号
• 去掉了冗余的中间responses字段提取逻辑，减少不必要的JSON解析开销

扩展场景（需保留动态UUID键） #

如果responses节点下可能存在多个UUID键，且需要同时返回对应的键值，可以使用以下版本：

SELECT 
    tipo,
    response_uuid,
    seq,
    response
FROM response r,
     JSON_TABLE(
         r.payload, 
         '$'
         COLUMNS (
             tipo VARCHAR2(64) PATH '$.type',
             NESTED PATH '$.data.responses.*'
             COLUMNS (
                 response_uuid VARCHAR2(64) PATH '$key',
                 NESTED PATH '$[*]'
                 COLUMNS (
                     seq FOR ORDINALITY,
                     response VARCHAR2(4000) PATH '$'
                 )
             )
         )
     ) jt;

内容的提问来源于stack exchange，提问作者Gustavo J. Moretti