How can I get the cartesian product in Oracle SQL of the result of a string split with all entries in an array both being keys in a JSON object?

Question

How can I get the cartesian product of the result of a string split with all entries in an array both being keys in a JSON object? This was the closest I got to the answer

SELECT REGEXP_SUBSTR(scp, '[^ ]+', 1, level), aud
  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

but it is only exploding each s for a1. I am looking to get 9 entries, mapping each s to each a. How can this be achieved with a single JSON_TABLE query?

Answer 1

You cannot split a string using JSON functions.

You can split the string using simple string functions (which are much faster than regular expressions) and a recursive sub-query factoring clause:

WITH rsqfc (scp, aud, spos, epos) AS (
  SELECT scp,
         aud,
         1,
         INSTR(scp, ' ', 1)
  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 '$[*]'
             )
           )
         )
UNION ALL
  SELECT scp,
         aud,
         epos + 1,
         INSTR(scp, ' ', epos + 1)
  FROM   rsqfc
  WHERE  epos > 0
)
SELECT CASE epos
       WHEN 0
       THEN SUBSTR(scp, spos)
       ELSE SUBSTR(scp, spos, epos - spos)
       END AS scp,
       aud
FROM   rsqfc;

From Oracle 12, you could also use CROSS APPLY:

SELECT REGEXP_SUBSTR(scp, '[^ ]+', 1, lvl) AS 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 '$[*]'
           )
         )
       )
       CROSS APPLY (
         SELECT LEVEL AS lvl
         FROM   DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT(scp, '[^ ]+')
       );

Which both output:

SCP	AUD
s1	a1
s1	a2
s1	a3
s2	a1
s2	a2
s2	a3
s3	a1
s3	a2
s3	a3

fiddle