Unexpected behavior in Oracle when using Group By with JSON_TABLE

Question

I have a denormalized VIEW we'll call VIEW_X which looks like the following (It's just a regular simple view - not materialized or anything like that):

ID   GROUP_ID   PART_1_ID                               PART_2_ID
1    1723189    cd69f0f4-a5ed-4196-916d-401e98ffec75    X1
1    1723189    cd69f0f4-a5ed-4196-916d-401e98ffec75    X2
2    1723185    8d5132cb-1b6e-4e79-9698-fd1962eb808f    K1
2    1723188    a191cb01-32ac-4ab4-bd6b-3ef777e395ca    K1

It's denormalized in that it actually represents a structure like this:

{
  id: 1,
  group_id: 1723189,
  part_1_id: 'cd69f0f4-a5ed-4196-916d-401e98ffec75'
  part_2_ids: ["X1", "X2"]
}

the PART_2_ID in this view is the result of selecting from a JSON_TABLE where the data in the original table is stored in an array like ["X1", "X2"]:

JSON_TABLE(a.PART_2_IDS, '$' COLUMNS (
            NESTED PATH '$[*]'
              COLUMNS (
                  PART_2_ID  VARCHAR2(4000) PATH '$'
                )
            )) p2

When I run a query like this on this view I get 0 results although the expected result is a single result with the ID of 2:

SELECT ID
FROM VIEW_X
WHERE PART_2_ID IN ('K1')
GROUP BY ID
HAVING COUNT(DISTINCT(PART_2_ID)) = 1

ID
--
(no results)

figure 1

Curiously enough if I run just the following I get the expected two results as there are two rows with ID 2 where there is a match on PART_2_ID as K1:

SELECT ID
FROM VIEW_X
WHERE PART_2_ID IN ('K1')

ID
--
 2
 2

If, however, I run either of the following queries I get a match on ID 1:

SELECT ID
FROM VIEW_X
WHERE PART_2_ID IN ('X1')
GROUP BY ID
HAVING COUNT(DISTINCT(PART_2_ID)) = 1

ID
--
 1

SELECT ID
FROM VIEW_X
WHERE PART_2_ID IN ('X1', 'X2')
GROUP BY ID
HAVING COUNT(DISTINCT(PART_2_ID)) = 2

ID
--
 1

I don't understand why figure 1 is not returning the expected result - is there something I'm overlooking? Is this a quirk with how JSON_TABLE works?

Might be useful to include the original JSON and your DB version info. Sounds like a bug though; maybe search MoS or raise an SR. — Alex Poole
– Alex Poole, Commented Jun 21, 2018 at 22:13
Might also be useful to have a complete minimal reproducible example including the full code for the view (rather than just the snippet for part_2_id) and the DDL statements for the underlying tables. — MT0
– MT0, Commented Jun 21, 2018 at 22:37

MT0 · Accepted Answer · 2018-06-21 22:25:49Z

2

I cannot replicate this in:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production; or
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production on https://livesql.oracle.com

Oracle Setup:

CREATE TABLE table1 ( document CLOB CONSTRAINT ensure_json CHECK (document IS JSON) );

INSERT INTO table1 ( document ) VALUES ( '{"id":1,"group_id":1723189,"part_1_id":"cd69f0f4-a5ed-4196-916d-401e98ffec75","part_2_ids":["X1","X2"]}' );
INSERT INTO table1 ( document ) VALUES ( '{"id":2,"group_id":1723185,"part_1_id":"8d5132cb-1b6e-4e79-9698-fd1962eb808f","part_2_ids":["K1"]}' );
INSERT INTO table1 ( document ) VALUES ( '{"id":2,"group_id":1723188,"part_1_id":"a191cb01-32ac-4ab4-bd6b-3ef777e395ca","part_2_ids":["K1"]}' );

CREATE VIEW VIEW_X AS
SELECT p.*
FROM   table1 t
       CROSS JOIN
       JSON_TABLE(
         t.document,
         '$'
         COLUMNS (
           id        PATH '$.id',
           group_id  PATH '$.group_id',
           part_1_id PATH '$.part_1_id',
           NESTED PATH '$.part_2_ids[*]'
             COLUMNS (
               PART_2_ID  VARCHAR2(4000) PATH '$'
             )
           )
         ) p;

Query 1:

SELECT *
FROM   VIEW_X;

Results:

        ID   GROUP_ID PART_1_ID                            PART_2_ID
---------- ---------- ------------------------------------ ---------
         1    1723189 cd69f0f4-a5ed-4196-916d-401e98ffec75 X1   
         1    1723189 cd69f0f4-a5ed-4196-916d-401e98ffec75 X2   
         2    1723185 8d5132cb-1b6e-4e79-9698-fd1962eb808f K1   
         2    1723188 a191cb01-32ac-4ab4-bd6b-3ef777e395ca K1

Query 2:

SELECT ID
FROM VIEW_X
WHERE PART_2_ID IN ('K1')
GROUP BY ID
HAVING COUNT(DISTINCT(PART_2_ID)) = 1;

Results:

ID
--
 2

answered Jun 21, 2018 at 22:25

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jordan Over a year ago

@MTO Thank you for the detailed response! This is in AWS RDS which only supports up to 12.1.0.1 - I will run a local test tomorrow morning, with my particular example, to verify it can be replicated as of course I'm obfuscating my real data (but this is very closely based on an actual example) - if I can reproduce I'll provide the full working example

Collectives™ on Stack Overflow

Unexpected behavior in Oracle when using Group By with JSON_TABLE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related