Nested JSON object from composite results - PostgreSQL

Question

Postgresql 17.
Having recipes management system with the following tables:

Chefs -> Recipes -> Ingredients
                 -> Sausages 

Each table contains a field with the id of its ancestor. What I am looking for is a query that return for the specified Chef Id a JSON with all of its full recipes (see example below), empty Recipes/Ingredients/Sausages must return just [].

Struggling with CTE's (is the right path?) and JSON functions (json_build_object, json_build_array, etc.) with no luck, I'm learning JSON functions...

Any help is apreciated.

[{"id": 1,
  "chefid": 1,
  "name": "Recipe 1",
  "description": "Sample recipe 1",
  "ingredients": [{"id": 1, "recipeid": 1, "name": "Name..."},
                  {"id": 2, "recipeid": 1, "name": "Name..."},
                  {"id": 3, "recipeid": 1, "name": "Name..."}],
  "sausages": [{"id": 1, "recipeid": 1, "name": "Name..."},
               {"id": 2, "recipeid": 1, "name": "Name..."},
               {"id": 3, "recipeid": 1, "name": "Name..."}]},
 {"id": 2,
  "chefid": 1,
  "name": "Recipe 2",
  "description": "Sample recipe 2",
  "ingredients": [{"id": 1, "recipeid": 2, "name": "Name..."},
                  {"id": 2, "recipeid": 2, "name": "Name..."},
                  {"id": 3, "recipeid": 2, "name": "Name..."}],
  "sausages": [{"id": 1, "recipeid": 2, "name": "Name..."},
               {"id": 2, "recipeid": 2, "name": "Name..."},
               {"id": 3, "recipeid": 2, "name": "Name..."}]}]

Table setup and example data: fiddle

CREATE TABLE chefs(id,name,surname)AS VALUES
 (1, 'Jane', 'Doe'),
 (2, 'Joe', 'Bloggs');
CREATE TABLE recipes(id,chef_id,name,description)AS VALUES
 (1, 1, 'Chicken pie', 'Chicken pie description'),
 (2, 1,'Sweet potato & peanut curry','Sweet potato & peanut curry description'),
 (3, 2, 'Beef stroganoff', 'Beef stroganoff description');
CREATE TABLE ingredients(id,name,description,recipe_id)AS VALUES
 (1, 'olive oil', 'description', 1),
 (2, 'garlic', 'description', 1),
 (3, 'plain flour', 'description', 1),
 (4, 'mushrooms', 'description', 2),
 (5, 'English mustard', 'description', 2),
 (6, 'sweet potato', 'description', 3);
CREATE TABLE sausages (id,name,description,recipe_id)AS VALUES
 (1, 'Battered sausage', 'description', 1),
 (2, 'Saveloy', 'description', 1),
 (4, 'Glamorgan sausage', 'description', 3);

Edit: Code (with nested query) with wrong output

SELECT json_build_object(
         'id',id,
         'name',name,
         'description',description,
         'ingredients',(SELECT json_build_object(
                                'name', ingredients.name,
                                'description', ingredients.description) 
                        FROM ingredients 
                        WHERE ingredients.id = recipes.id),
         'sausages',   (SELECT json_build_object(
                                'name', sausages.name,
                                'description', sausages.description) 
                        FROM sausages 
                        WHERE sausages.id = recipes.id)
      ) 
FROM recipes 
WHERE chef_id = 1;

Answer 1

Your attempt is quite close already, but

• you have the wrong join condition (id instead of recipe_id) in your subqueries
• your subqueries don't return arrays

The simplest way to achieve that is to just use the ARRAY constructor around the subquery:

SELECT json_build_object(
  'id', id,
  'name', name,
  'description', description,
  'ingredients', ARRAY(
    SELECT json_build_object(
      'name', ingredients.name,
      'description', ingredients.description
    )
    FROM ingredients
    WHERE ingredients.recipe_id = recipes.id
  ),
  'sausages', ARRAY(
    SELECT json_build_object(
      'name', sausages.name,
      'description', sausages.description
    )
    FROM sausages
    WHERE sausages.recipe_id = recipes.id
  )
)
FROM recipes
WHERE chef_id = 1;

^{(online demo)}

Alternatively, use the json_agg aggregate function in the subquery, but that means the subquery returns NULL when no rows are found and you'd have to wrap it in COALESCE(…, '[]'::json):

SELECT json_agg(json_build_object(
  'id',id,
  'name',name,
  'description',description,
  'ingredients', COALESCE((
    SELECT json_agg(json_build_object(
      'name', ingredients.name,
      'description', ingredients.description
    ))
    FROM ingredients
    WHERE ingredients.recipe_id = recipes.id
  ), '[]'),
  'sausages', COALESCE((
    SELECT json_agg(json_build_object(
      'name', sausages.name,
      'description', sausages.description
    ))
    FROM sausages
    WHERE sausages.recipe_id = recipes.id
  ), '[]')
))
FROM recipes
WHERE chef_id = 1;

^{(online demo, with jsonb instead of json for formatting via jsonb_pretty)}