Group table in a dict or json

Question

I have two tables with a 1:M relation, and I'd like to group this relation in a dict or JSON.

My data is something like: (id, provider_code, provider)

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;;""

What I'm looking for is something like this:

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

I don't mind if the aggregation is in JSON format, but I need it to be easy to parse.

I've tried with postgres function array_agg but although I've parsed to varchar, still got an error:

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

This is the query I was trying, but I know it is a problem with parameters, I'd need a way to pass 2 parameters instead only one.

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

Table definitions: Properties table:

"id";"integer"

Providers table:

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

Please show us your query, we can't really help without it (although from the error message it seems you're passing to parameter to `ARRAY_AGG`, but it expects a single one). — Marth, Jun 27 '17 at 07:36
I know, this is the point, but I if array_aag only by one, I can't know which is the code of each provider, so I think I'd need a dict or JSON — Pablo Pardo, Jun 27 '17 at 07:42
Why doesn't the code `2450` appear in `41108`'s expected result? — Marth, Jun 27 '17 at 07:57
The result is only a sample to show the structure that I'm looking for , @Marth — Pablo Pardo, Jun 27 '17 at 07:58

score 1 · Answer 1 · answered Jun 27 '17 at 07:59

You can use jsonb_build_object() to create an json object for each code/id_provider tuple and use jsonb_agg to aggregate those in a json array:

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)

If you don't want to keep rows where code IS NULL simply add a WHERE clause:

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)

This is is a good point, but still get an error: jsonb_build_object(text, integer) does not exist. Maybe is a problem with postgres version? — Pablo Pardo, Jun 27 '17 at 08:07
@ppardoz: What version are you using? `jsonb` is only available in 9.4+. If you're using 9.2 or 9.3 replace `jsonb` with `json` in the function names to create a `json` typed result. — Marth, Jun 27 '17 at 08:10

score 1 · Answer 2 · answered Jun 27 '17 at 08:12

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;

Richard Mohammed · Answer 3 · 2017-06-27T08:37:04.260

0

You can try using the string concatenation operator and then aggregating as follows:

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

See the following SQL fiddle: link

edited Jun 27 '17 at 08:37

answered Jun 27 '17 at 08:15

Richard Mohammed

84
4

Unless you really don't have any other choice, I really advise strongly against building JSON yourself. You always end up forgetting escaping data, which will lead at best to bugs, at worst to security issues. – jcaron Jun 27 '17 at 09:07
@jcaron good point. I'm sure there is a better solution but if needed the values can also be wrapped in a to_json(). – Richard Mohammed Jun 27 '17 at 11:04

Group table in a dict or json

3 Answers3