Блог

Я хочу создать SQL-запрос (postgres), который возвращает объект (JSON) с динамическими ключами. Поэтому я создал этот пример таблиц с некоторыми значениями.

 CREATE TABLE foods (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE nutrients (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE foods_nutrients (
  food_id int REFERENCES foods(id) ON UPDATE CASCADE ON DELETE CASCADE,
  nutrient_id int REFERENCES nutrients(id) ON UPDATE CASCADE ON DELETE CASCADE,
  amount DECIMAL NOT NULL,
  CONSTRAINT food_nutrient_pk PRIMARY KEY (food_id, nutrient_id)
);

INSERT INTO foods(name)
VALUES  ('Apple'),
        ('Banana');
INSERT INTO nutrients(name)
VALUES  ('Carbohydrates'),
        ('Protein'),
        ('Fat');
INSERT INTO foods_nutrients(food_id, nutrient_id, amount)
VALUES  (1, 1, 14.0),
        (1, 2, 0.3),
        (1, 3, 0.2),
        (2, 1, 23.7),
        (2, 2, 1.1);
  

Результат запроса должен выглядеть как этот JSON, если это возможно. Или, по крайней мере, достаточно близко. Я думаю, основной проблемой является объект с идентификаторами питательных веществ в качестве ключей.

 [
  {
    "id": 1,
    "name": "Apple",
    "nutrients": {
      "1": 14.0,
      "2": 0.3,
      "3": 0.2
    }
  },
  {
    "id": 2,
    "name": "Banana",
    "nutrients": {
      "1": 23.7,
      "2": 1.1
    }
  }
]
  

В основном JSON_BUILD_OBJECT() функция необходима для построения комбинации между id name nutrients значениями и . Но nutrients требуется еще одна операция, которая будет использоваться JSON_OBJECT_AGG() для получения более сложного объекта. Итак, рассмотрите возможность использования

 WITH fn1 AS
(
SELECT fn.food_id, f.name,
       JSON_OBJECT_AGG( nutrient_id, amount ) AS nutrients                        
  FROM foods_nutrients fn 
  JOIN foods f 
    ON fn.food_id=f.id
  JOIN nutrients n 
    ON fn.nutrient_id=n.id
 GROUP BY fn.food_id, f.name  
)
SELECT JSON_AGG(
                JSON_BUILD_OBJECT( 'id', food_id, 
                                   'name', name,
                                   'nutrients', nutrients) 
                         ) AS js                
  FROM fn1
  

Demo

Кстати, использование JSONB эквивалентов тех функций, вместе с JSONB_PRETTY() которыми вложены наши текущие результаты, даст хороший дизайн в виде Demonstrated, точно так же, как формат в вопросе :

 [
    {
        "id": 1,
        "name": "Apple",
        "nutrients": {
            "1": 14.0,
            "2": 0.3,
            "3": 0.2
        }
    },
    {
        "id": 2,
        "name": "Banana",
        "nutrients": {
            "1": 23.7,
            "2": 1.1
        }
    }
]
  

Вы можете попробовать приведенную ниже демонстрацию здесь

 select row_to_json(fv)
      from (
        select food_id,f.name,json_agg(
                 json_build_object(nutrient_id ,amount) 
            )as nutrients
from foods_nutrients fn join foods f on fn.food_id=f.id
join nutrients n on fn.nutrient_id=n.id group by food_id,f.name   
      ) fv