How to filter with group by or distinct clause in constricting oracle json_array

Question

Having simple database structure

PERSON_ID   FIRST_NAME  LAST_NAME
1             John          Doe
2             John          Doe
3             Peter       Jackson

need to construct single row output with JSON ARRAY structure containing unique data filtered by first_name,last_name criteria.

Expected outcome:

[{
    "firstname": "John",
    "lastname": "Doe"
},
{
    "firstname": "Peter",
    "lastname": "Jackson"
}]

Using group by on array level results in two rows

SELECT  json_array(   
                    json_object(  key 'firstname' VALUE t.first_name, 
                                  key 'lastname'  VALUE t.last_name)

                 ) RESPONSEJSON

FROM TESTDATA t
GROUP BY t.first_name, t.last_name



    RESPONSEJSON
1   [{"firstname":"Peter","lastname":"Jackson"}]
2   [{"firstname":"John","lastname":"Doe"}]

Chris Saxon · Accepted Answer · 2020-06-04 12:34:20Z

4

Use a subquery to create distinct objects. Then aggregate them together in the array:

create table t (
  person_id int, first_name varchar2(10), last_name varchar2(10)
);

insert into t values (1, 'John', 'Doe' );
insert into t values (2, 'John', 'Doe' );
insert into t values (3, 'Peter', 'Jackson' );
commit;

with jobjs as (
  select  distinct json_object(  
            key 'firstname' VALUE t.first_name, 
            key 'lastname'  VALUE t.last_name
          ) responsejson
  from    t
)
SELECT  json_arrayagg ( responsejson )
FROM    jobjs;

[
  {"firstname":"John","lastname":"Doe"},
  {"firstname":"Peter","lastname":"Jackson"}
]

There's a bug in 12.2 which gives wrong results when using distinct as above. You can get around this with group by instead:

with jobjs as (
  select  json_object(  
            key 'firstname' VALUE t.first_name, 
            key 'lastname'  VALUE t.last_name
          ) responsejson
  from    t
  group   by t.first_name, t.last_name
)
SELECT  json_arrayagg ( responsejson )
FROM    jobjs;

[
  {"firstname":"Peter","lastname":"Jackson"},
  {"firstname":"John","lastname":"Doe"}
]

edited Jun 4, 2020 at 12:34

answered Jun 4, 2020 at 9:58

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

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

5 Comments

Chris Saxon Over a year ago

It is a single query?

Nikolay Marinov Over a year ago

Sorry, my bad, never used WITH SELECT before. But with your example, the result is: [{"firstname":"John","lastname":"Doe"},{"firstname":"John","lastname":"Doe"},{"firstname":"Peter","lastname":"Jackson"}]

Chris Saxon Over a year ago

Which version of Oracle Database are you using?

Chris Saxon Over a year ago

There's a 12.2 bug (fixed in 19c) with distinct; you can use group by instead

Nikolay Marinov Over a year ago

12.2, with group by all goes fine. Thanks Chris

Collectives™ on Stack Overflow

How to filter with group by or distinct clause in constricting oracle json_array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related