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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related