Групповой стол в dict или json

Question

У меня есть две таблицы с отношением 1:M, и я хотел бы сгруппировать это отношение в dict или JSON.

Мои данные что-то вроде: (идентификатор, код поставщика, провайдера)

41108;2450;"provider1"
41108;1064389;"provider4"
41108;609920;"provider2"
41108;540582;"provider3"
41108;228268;"provider2"
41108;169483;"provider2"
41108;93361;"provider2"
41108;47723;"provider1"
41109;2658780;"provider4"
41109;62348;"provider1"
41109;320588;"provider3"
41109;323161;"provider3"
42500;;""

Я ищу что-то вроде этого:

41108;{"provider1":540582, "provider2":1064389, "provider3":228268, "provider3":93361};
41109;{"provider1":989301, "provider2":98536, "provider3":323161, "provider3":47854}

Я не против, если агрегация в формате JSON, но мне нужно, чтобы ее было легко разобрать.

Я пытался с помощью функции postgres array_agg, но, хотя я проанализировал varchar, все равно получил ошибку:

ERROR:  function array_agg(character varying, character varying) does not exist

Это запрос, который я пробовал, но я знаю, что это проблема с параметрами, мне нужен способ передать 2 параметра вместо одного.

select  id,  array_agg(code::varchar, id_provider::varchar)
from properties
full outer join providers 
on properties.id = providers.id 
group by id,  code, id_provider
order by id, code

Определения таблиц: Таблица свойств:

"id";"integer"

Таблица провайдеров:

"id";"integer"
"provider_code";"integer"
"provider";"character varying"

Answer 1

Ты можешь использовать jsonb_build_object() создать объект JSON для каждого code/id_provider кортеж и использование jsonb_agg объединить их в массиве json:

WITH data(id, code, id_provider) AS (
  VALUES
    (41108,2450,'provider1'),
    (41108,1064389,'provider4'),
    (41108,609920,'provider2'),
    (41108,540582,'provider3'),
    (41108,228268,'provider2'),
    (41108,169483,'provider2'),
    (41108,93361,'provider2'),
    (41108,47723,'provider1'),
    (41109,2658780,'provider4'),
    (41109,62348,'provider1'),
    (41109,320588,'provider3'),
    (41109,323161,'provider3'),
    (42500,NULL,'')
)
SELECT id, jsonb_agg(jsonb_build_object(id_provider, code))
FROM data
GROUP BY id
;
┌───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  id   │                                                                                       jsonb_agg                                                                                       │
├───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 41109 │ [{"provider4": 2658780}, {"provider1": 62348}, {"provider3": 320588}, {"provider3": 323161}]                                                                                          │
│ 41108 │ [{"provider1": 2450}, {"provider4": 1064389}, {"provider2": 609920}, {"provider3": 540582}, {"provider2": 228268}, {"provider2": 169483}, {"provider2": 93361}, {"provider1": 47723}] │
│ 42500 │ [{"": null}]                                                                                                                                                                          │
└───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(3 rows)

---

Если вы не хотите хранить строки где code IS NULL просто добавьте WHERE пункт:

WITH data(id, code, id_provider) AS (
  VALUES
    (41108,2450,'provider1'),
    (41108,1064389,'provider4'),
    (41108,609920,'provider2'),
    (41108,540582,'provider3'),
    (41108,228268,'provider2'),
    (41108,169483,'provider2'),
    (41108,93361,'provider2'),
    (41108,47723,'provider1'),
    (41109,2658780,'provider4'),
    (41109,62348,'provider1'),
    (41109,320588,'provider3'),
    (41109,323161,'provider3'),
    (42500,NULL,'')
)
SELECT id, jsonb_agg(jsonb_build_object(id_provider, code))
FROM data
WHERE code IS NOT NULL
GROUP BY id
;
┌───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  id   │                                                                                       jsonb_agg                                                                                       │
├───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 41109 │ [{"provider4": 2658780}, {"provider1": 62348}, {"provider3": 320588}, {"provider3": 323161}]                                                                                          │
│ 41108 │ [{"provider1": 2450}, {"provider4": 1064389}, {"provider2": 609920}, {"provider3": 540582}, {"provider2": 228268}, {"provider2": 169483}, {"provider2": 93361}, {"provider1": 47723}] │
└───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(2 rows)

Answer 2

SELECT id,
       CASE
          WHEN min(pv.provider) IS NULL
          THEN NULL::jsonb
          ELSE jsonb_agg(
                  jsonb_build_object(
                     COALESCE(pv.provider, ''),
                     pv.provider_code
                  )
               )
          END
FROM properties pp
   LEFT JOIN providers pv
      USING (id)
GROUP BY id;

Answer 3

Вы можете попробовать использовать оператор конкатенации строк, а затем выполнить агрегирование следующим образом:

select  properties.id, 
array_to_string(array_agg('{"' ||providers.provider || '": ' || providers.code || '}'), ',') as t
from properties
full outer join providers 
on properties.id = providers.id 
group by properties.id, providers.id
order by properties.id, t

Смотрите следующую скрипту SQL: ссылка