Agfdhyk

I have the following three tables:

drinks: id | name

ingredient: id | name

drink_ingredients: drinks.id | ingredients.id

When I create a "drink", I provide an array of ingredient ids as well. The relationship here is managed by the drink_ingredients table, which allows me to reuse ingredients.

I'm trying to create a query of which will return data representing the following:

drink { id: 0 | name: 'Coffee' | ingredients: [2, 3] }

Essentially meaning I would like to extract out the ingredient id attached to this drink and return them as an array.

I currently have

select * from "drinks" inner join "drink_ingredients" on "drinks"."id" = "drink_ingredients"."drink_id"

I'm still missing the step to retrieve the data from ingredients, but as well this, this returns:

  {

    "id": 0,

    "owner": 18,

    "name": "Coffee",

    "drink_id": 0,

    "ingredient_id": 2

  },

  {

    "id": 0,

    "owner": 18,

    "name": "Coffee",

    "drink_id": 0,

    "ingredient_id": 3

  },

Is it possible to correctly embed data this way in a single statement?

This can be done using the array_agg function:

select d.id, d.name, array_agg(di.ingredient_id) as ingredients

from drinks d

  left outer join drink_ingredients di on di.drink_id = d.id

group by d.id, d.name

As you (per your question) only want the array of ingredient-ids, you don't even need to join with ingredients. The left join is just in case you have a drink with no ingredients (water?).