Complex Json file and Oracle JSON_TABLE results in more than one row

Question

I have a complex Json file (in a CLOB). I'll try to make it esential:

{
  "c1": "c1",
  "c2": "c2",
  "c3": {
    "a": "a"
  },
  "c4": {
    "c5": {
      "c6": "c6"
    }
  }
}

So I have a query like this:

SELECT tabj.*
  FROM table_with_clob_column t,
       JSON_TABLE(
                   t.clob_with_json_file,
                   '$' COLUMNS(
                                c1,
                                c2,
                                NESTED c3 COLUMNS(a),
                                NESTED c4.c5 COLUMNS(c6)
                               )
                  ) tabj
 WHERE t.id = 1; -- just one row in t table

The problem is that I get more than one row:

c1	c2	a	c6
c1	c2	a
c1	c2		c6

I need one row:

c1	c2	a	c6
c1	c2	a	c6

Answer 1

You do not appear to need to use NESTED, instead use the path to reference the descendant attributes directly:

select tabj.*
from   table_with_clob_column t
       CROSS APPLY JSON_TABLE(
         t.clob_with_json_file,
         '$'
         columns(
           c1,
           c2,
           a PATH '$.c3.a',
           c6 PATH '$.c4.c5.c6'
        )
      ) tabj
where t.id = 1

Which, for the sample data:

CREATE TABLE table_with_clob_column (
  id NUMBER,
  clob_with_json_file CLOB CHECK (clob_with_json_file IS JSON)
);

INSERT INTO table_with_clob_column (id, clob_with_json_file)
VALUES (1, '{ "c1": "c1", "c2": "c2", "c3": { "a": "a" }, "c4": { "c5": { "c6": "c6" } } }');

Outputs:

C1	C2	A	C6
c1	c2	a	c6

fiddle