How to combine two postgres columns to single value (json object)

Question

I'm trying to write a query on my food.list table:

categories | items ---------------------------- dairy | ["milk", "cheese"] fruit | ["apples", "pears", "grapes"] vegetables | ["carrots"]

and return a selected_foods column with a single row. I want the row's value to be an object with a list of categories (keys) with an array of items (values).

selected_foods ------------------------------------ { dairy: ["milk", "cheese"], fruit: ["apples", "pears", "grapes"], vegetables: ["carrots"] }

So far, I've tried:

SELECT json_agg(json_build_object(categories, items::json))::json AS selected_foods FROM food.list

But this returns an array of objects, i.e.:

selected_foods ------------------------------------ [ { dairy: ["milk", "cheese"] }, { fruit: ["apples", "pears", "grapes"] }, { vegetables: ["carrots"] } ]

I think I'm applying the json_build_object function too early...or maybe I need to unwrap them again after?

Any help would be awesome, thanks :)

Kaushik Nayak · Accepted Answer · 2019-05-28 04:42:05Z

2

Use json_object_agg

SELECT json_object_agg( categories, items::json) as selected_foods from list

DEMO

answered May 28, 2019 at 4:42

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

How to combine two postgres columns to single value (json object)

1 Answer 1

Comments

Hot Network Questions