Postgres Creating JSON Object from Aggregated Rows

Question

I'm having some trouble creating a JSON object where the keys of the object are the values on which I aggregate rows in Postgres.

Here is the table I'm working with:

create table if not exists safety_training_options (
  id serial primary key,
  option_type text not null,
  name text not null
)

And some sample data:

insert into safety_training_options (option_type, name)
values ('category', 'General Industry'),
       ('category', 'Maritime'),
       ('category', 'Construction'),
       ('frequency', 'Daily'),
       ('frequency', 'Weekly'),
       ('frequency', 'Bi-weekly'),
       ('method', 'Online'),
       ('method', 'Classroom');

Here is my query so far, which will get me the aggregated rows:

select 
    option_type as type,
    json_agg(sto.name) as options
from safety_training_options as sto
group by sto.option_type;

Result set:

╔════════════╦═════════════════════════╗
║    type    ║         options         ║
╠════════════╬═════════════════════════╣
║ method     ║ ["Online", "Classroom"] ║
║ frequency  ║ ["Daily, "Weekly", ...] ║
║ class_type ║ [...]                   ║
║ category   ║ [...]                   ║
╚════════════╩═════════════════════════╝

Where I'm stuck is how to build a json object where the keys are the values in the type column and the values are the arrays in the options column. I want my final result to look like this:

{
    "method": [...],
    "category": [...],
    "frequency": [...],
    "class_type": [...]
}

A bonus question is can I rename the values to pluralize them? It would be great if I could make the keys in the json object plural like "methods" "categories" "frequencies" and "class_types". I know I could just change the values in the table to be plural, but I curious if there is another way I can build a custom json object.

Michel Milezzi · Accepted Answer · 2017-11-07 19:52:23Z

15

Just use json_object_agg:

WITH tmp AS (
    SELECT 
        option_type,
        json_agg(sto.name) as training_options
    FROM 
        safety_training_options as sto
    GROUP BY 
        sto.option_type
)
SELECT json_object_agg(option_type, training_options) FROM tmp

answered Nov 7, 2017 at 19:52

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

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

Comments

Parfait · Accepted Answer · 2017-11-07 19:48:53Z

Consider row_to_json with conditional array_agg:

SELECT row_to_json(r) as output
FROM 
(  
   ( SELECT array_remove(array_agg(CASE WHEN s.option_type = 'category' 
                                        THEN s.name ELSE NULL END), NULL) AS category,
            array_remove(array_agg(CASE WHEN s.option_type = 'frequency' 
                                        THEN s.name ELSE NULL END), NULL) AS frequency,
            array_remove(array_agg(CASE WHEN s.option_type = 'method' 
                                        THEN s.name ELSE NULL END), NULL) AS method
     FROM safety_training_options s
    )      
) r;

-- {"category":["General Industry","Maritime","Construction"],
--  "frequency":["Daily","Weekly","Bi-weekly"],
--  "method":["Online","Classroom"]}

Rextester Demo

Collectives™ on Stack Overflow

Postgres Creating JSON Object from Aggregated Rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related