我想创建一个(postgres)SQL查询,该查询返回带有动态键的(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为键的对象。
[
{
"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
顺便说一句,使用JSONB
这些函数的等效项以及JSONB_PRETTY()
嵌套我们的当前结果将产生一个很好的设计Demo,就像问题中的格式一样:
[
{
"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
}
}
]
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句