将SQL查询中的数据格式化为JSON
将SQL查询中的数据格式化为JSON
提问于 2019-06-18 02:12:24
我有一个表单的数据库表
CREATE TABLE products(
id SERIAL PRIMARY KEY,
category TEXT,
name TEXT,
price REAL,
weight REAL);并且我想在表单中为我的API输出JSON
[{"category":"category name",
"products":[{"name":"product name one","price":4.44,"weight":0.5},
{"name":"product name two","price":6.45,"weight":1.0}]}]因此，如果我的表包含以下条目：
INSERT INTO products (category, name, price, weight)
VALUES ("toothpaste","Chrest Pro-White",4.99,6.4),
("toothpaste","Colgate Cavity Prevention",3.99,6.4),
("body wash","Old Spice Hydro",5.99,16.0),
("body wash","Axe Apollo",8.99,22.0);该API应返回
[{"category":"toothpaste",
"products":[{"name":"Chrest Pro-White","price":4.99,"weight":6.4},
{"name":"Colgate Cavity Prevention","price":3.99,"weight":6.4}]},
{"category":"body wash",
"products":[{"name":"Old Spice Hydro","price":5.99,"weight":16.0},
{"name":"Axe Apollo","price":8.99,"weight":22.0}]}]我可以遍历表中的所有行，并为每个唯一的类别名称手动创建json对象，但我希望有更好的方法来做到这一点。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-06-18 02:26:03
通过使用ORM并从API服务器创建json，有很多方法可以做到这一点，但也可以直接在postgres中完成：
select json_agg(categories)
FROM (
SELECT json_build_object(
'category', category,
'products', json_agg(
json_build_object(
'name', name,
'price', price,
'weight', weight)
)
) as categories
FROM products
GROUP BY category) sub;
[{"category" : "toothpaste", "products" : [{"name" : "Chrest Pro-White", "price" : 4.99, "weight" : 6.4}, {"name" : "Colgate Cavity Prevention", "price" : 3.99, "weight" : 6.4}]}, {"category" : "body wash", "products" : [{"name" : "Old Spi
ce Hydro", "price" : 5.99, "weight" : 16}, {"name" : "Axe Apollo", "price" : 8.99, "weight" : 22}]}]Stack Overflow用户
发布于 2019-06-18 02:19:51
您可以使用此数据(JSON.stringify(console.log))；
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56636745
复制相关文章
相似问题
腾讯云开发者
