CODEWITHSUNDEEP
sqlarraysjsonpostgresql
watsbeat
watsbeat

Reputation: 151

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

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 :)

Upvotes: 1

Views: 1257

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Use json_object_agg

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

DEMO

Upvotes: 2

Related Questions