Как получить уникальные данные с GROUP BY или DISTINCT и вывести одну строку JSON_ARRAY?

Question

Есть такие данные в таблице:

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

Tребуется вывести одну строку со структурой JSON массива, содержащую уникальные данные, отфильтрованные по столбцам first_name, last_name.

То есть, ожидаемый вывод:

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

Пробую с GROUP BY на уровне массива, но так запрос выводит две строки:

SELECT json_array(   
            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:

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

_{Свободный перевод вопроса How to filter with group by or distinct clause in constricting json_array от участника @Nikolay Marinov}

Answer 1

Используйте подзапрос для создания JSON объектов с DISTINCT, затем агрегируйте их в массив:

create table t (person_id, first_name, last_name) as
    select 1, 'John',  'Doe'     from dual union all
    select 2, 'John',  'Doe'     from dual union all
    select 3, 'Peter', 'Jackson' from dual;

with jo 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 jo;
    
[
  {"firstname":"John","lastname":"Doe"},
  {"firstname":"Peter","lastname":"Jackson"}
]

В 12.2 есть баг, который дает неверные результаты при использовании DISTINCT, как описано выше. Его можно обойти с помощью GROUP BY:

with jo 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 jo;

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

_{Свободный перевод ответа от участника @Chris Saxon}

БЛОГ НА HUSL

Как получить уникальные данные с GROUP BY или DISTINCT и вывести одну строку JSON_ARRAY?

Ответы (1 шт):