如何在Oracle SQL中用单JSON_TABLE实现拆分字符串与JSON数组的笛卡尔积

修正原有代码（基于CONNECT BY） #

你原来的查询问题出在CONNECT BY条件未限定分组，导致无法为每个aud独立拆分s字符串。只需添加分组限定和防循环条件，即可得到9条笛卡尔积记录：

SELECT REGEXP_SUBSTR(scp, '[^ ]+', 1, level) AS s_val, aud AS a_val
FROM (
    SELECT scp, aud
    FROM JSON_TABLE(
        '{
            "s": "s1 s2 s3",
            "a": ["a1", "a2", "a3"]
        }' FORMAT JSON,
        '$' COLUMNS(
            scp VARCHAR2 PATH '$.s',
            NESTED PATH '$.a[*]' COLUMNS(aud VARCHAR2 PATH '$')
        )
    )
)
CONNECT BY REGEXP_SUBSTR(scp, '[^ ]+', 1, level) IS NOT NULL
AND PRIOR aud = aud
AND PRIOR SYS_GUID() IS NOT NULL;

纯JSON_TABLE实现（推荐） #

无需使用CONNECT BY，先将空格分隔的s字段转换为JSON数组，再通过两个NESTED PATH遍历数组元素，自然生成笛卡尔积：

通用版本（兼容Oracle 12c+） #

SELECT s_item, a_item
FROM JSON_TABLE(
    REGEXP_REPLACE(
        '{
            "s": "s1 s2 s3",
            "a": ["a1", "a2", "a3"]
        }',
        '"s": "([^"]+)"',
        '"s": ["' || REPLACE('\1', ' ', '","') || '"]'
    ) FORMAT JSON,
    '$' COLUMNS(
        NESTED PATH '$.s[*]' COLUMNS(s_item VARCHAR2 PATH '$'),
        NESTED PATH '$.a[*]' COLUMNS(a_item VARCHAR2 PATH '$')
    )
);

Oracle 19c+优化版本 #

使用JSON_TRANSFORM更优雅地转换s字段为数组：

SELECT s_item, a_item
FROM JSON_TABLE(
    JSON_TRANSFORM(
        '{
            "s": "s1 s2 s3",
            "a": ["a1", "a2", "a3"]
        }',
        SET '$.s' = JSON(
            '["' || REPLACE(JSON_VALUE('$.s'), ' ', '","') || '"]'
        )
    ) FORMAT JSON,
    '$' COLUMNS(
        NESTED PATH '$.s[*]' COLUMNS(s_item VARCHAR2 PATH '$'),
        NESTED PATH '$.a[*]' COLUMNS(a_item VARCHAR2 PATH '$')
    )
);

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