In

the following query

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j,
       JSON_TABLE(j.data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt;

means that two table json_documents and jt are inner joined. In this web-site, it mentioned that the above query is equvalent the following:

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j JOIN
       JSON_TABLE(j.data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt ON 1=1;

But I can't understand how they are joined! Where is join column and what is the meaning of 1=1

In https://oracle-base/articles/19c/sql-nested-clause-instead-of-json_table-19c

the following query

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j,
       JSON_TABLE(j.data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt;

means that two table json_documents and jt are inner joined. In this web-site, it mentioned that the above query is equvalent the following:

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j JOIN
       JSON_TABLE(j.data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt ON 1=1;

But I can't understand how they are joined! Where is join column and what is the meaning of 1=1

• oracle-database
• json-table

1 Answer 1

A comma-join is the equivalent of a ANSI CROSS JOIN:

SELECT j.id, jt.first_name
FROM   json_documents j
       CROSS JOIN JSON_TABLE(
         j.data,
         '$'
         COLUMNS (
           first_name    VARCHAR2(50 CHAR)  PATH FirstName
         )
       ) jt;

A CROSS JOIN is the same as an INNER JOIN with no filter on the join:

SELECT j.id, jt.first_name
FROM   json_documents j
       INNER JOIN JSON_TABLE(
         j.data,
         '$'
         COLUMNS (
           first_name    VARCHAR2(50 CHAR)  PATH FirstName
         )
       ) jt
       ON (1=1);

There is no join column as the join is a CROSS JOIN and not an INNER JOIN. 1=1 is used to provide a condition for the join that is always true which effectively converts the JOIN back to a CROSS JOIN as every combination of rows will match the filter.

Technically, there is a column that joins the JSON_TABLE to the driving table as the JSON_TABLE uses j.data - it isn't in the JOIN condition though, it is embedded in the JSON_TABLE.

From Oracle 12 and later, you could equivalently use:

SELECT j.id, jt.first_name
FROM   json_documents j
       CROSS APPLY JSON_TABLE(
         j.data,
         '$'
         COLUMNS (
           first_name    VARCHAR2(50 CHAR)  PATH FirstName
         )
       );

or:

SELECT j.id, jt.first_name
FROM   json_documents j
       CROSS JOIN LATERAL (
         SELECT *
         FROM   JSON_TABLE(
                  j.data,
                  '$'
                  COLUMNS (
                    first_name    VARCHAR2(50 CHAR)  PATH FirstName
                  )
                )
       );

The syntax of both these queries indicates that json_documents is the driving table and the JSON_TABLE is applied to each row of the driving table.