如何从相关的数据库表中准备JSON对象和数组？
我正在节点中使用一个简单的REST API，我想在SQLite中创建一个查询，该查询将返回所有产品及其可能的选项。
这是我的数据库：
BEGIN TRANSACTION;
PRAGMA foreign_keys = OFF;
CREATE TABLE IF NOT EXISTS "product_options" (
"id" INTEGER,
"product_id" INTEGER,
"name" TEXT NOT NULL,
"value" TEXT NOT NULL,
PRIMARY KEY("id"),
FOREIGN KEY("product_id") REFERENCES "products"("id")
);
CREATE TABLE IF NOT EXISTS "products" (
"id" INTEGER,
"name" TEXT NOT NULL,
"brand_id" INTEGER,
PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "brands" (
"id" INTEGER,
"name" TEXT NOT NULL,
PRIMARY KEY("id")
);
INSERT INTO "product_options" VALUES (1,1,'color','black');
INSERT INTO "product_options" VALUES (2,1,'color','white');
INSERT INTO "product_options" VALUES (3,2,'color','indigo');
INSERT INTO "product_options" VALUES (4,3,'color','black');
INSERT INTO "product_options" VALUES (5,1,'size','9');
INSERT INTO "products" VALUES (1,'t-shirt',1);
INSERT INTO "products" VALUES (2,'jeans',3);
INSERT INTO "products" VALUES (3,'shoes',2);
INSERT INTO "products" VALUES (4,'shirt',NULL);
INSERT INTO "brands" VALUES (1,'acme');
INSERT INTO "brands" VALUES (2,'wonka');
INSERT INTO "brands" VALUES (3,'gekko');
INSERT INTO "brands" VALUES (4,'stark');
PRAGMA foreign_keys = ON;
COMMIT;
这是查询产品时我的API端点应产生的示例JSON输出：
{
"id": 1,
"name": "t-shirt",
"brand": "acme",
"options": {
"color": ["black", "white"],
"size": ["9"]
}
}
到目前为止，我已经提出了查询的第一部分，但是我无法弄清楚如何输出选项：
SELECT DISTINCT
products.id,
products.name,
brands.name AS brand,
product_options.value AS option_value
FROM products
INNER JOIN brands on brands.id = products.brand_id
INNER JOIN product_options on product_options.product_id = products.id
WHERE products.id = 1
这将返回以下结果：
id name brand option_value
1 t-shirt acme black
1 t-shirt acme white
1 t-shirt acme 9
但是，我想要的输出是：
id name brand color size
1 t-shirt acme black, white 9
当然，查询必须是“动态的”，因此将来也应返回添加到产品中的任何其他选项，例如：
id name brand color size condition
1 t-shirt acme black, white 9 new, used
任何想法我如何实现这一目标？我知道我可以简单地创建两个端点，一个端点检索产品列表，另一个端点返回其选项，但是将所有端点合并在一个文档中似乎是一个更好的解决方案。
任何帮助将不胜感激，谢谢！
回答如下：您可以使用标准的JSON1模块和一堆聚合子查询来直接从sqlite生成JSON：
SELECT
json_object('id', p.id
, 'name', p.name
, 'brand', b.name
, 'options', (SELECT json_group_object(name, ja)
FROM (SELECT po.name
, json_group_array(po.value) AS ja
FROM product_options AS po
WHERE po.product_id = p.id
GROUP BY po.name)))
FROM products AS p
JOIN brands AS b ON p.brand_id = b.id
WHERE p.id = 1;
{"id":1,"name":"t-shirt","brand":"acme","options":{"color":["black","white"],"size":["9"]}}
为了获得最佳结果，请在product_options(product_id, name)上添加索引。
如何从相关的数据库表中准备JSON对象和数组？
我正在节点中使用一个简单的REST API，我想在SQLite中创建一个查询，该查询将返回所有产品及其可能的选项。
这是我的数据库：
BEGIN TRANSACTION;
PRAGMA foreign_keys = OFF;
CREATE TABLE IF NOT EXISTS "product_options" (
"id" INTEGER,
"product_id" INTEGER,
"name" TEXT NOT NULL,
"value" TEXT NOT NULL,
PRIMARY KEY("id"),
FOREIGN KEY("product_id") REFERENCES "products"("id")
);
CREATE TABLE IF NOT EXISTS "products" (
"id" INTEGER,
"name" TEXT NOT NULL,
"brand_id" INTEGER,
PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "brands" (
"id" INTEGER,
"name" TEXT NOT NULL,
PRIMARY KEY("id")
);
INSERT INTO "product_options" VALUES (1,1,'color','black');
INSERT INTO "product_options" VALUES (2,1,'color','white');
INSERT INTO "product_options" VALUES (3,2,'color','indigo');
INSERT INTO "product_options" VALUES (4,3,'color','black');
INSERT INTO "product_options" VALUES (5,1,'size','9');
INSERT INTO "products" VALUES (1,'t-shirt',1);
INSERT INTO "products" VALUES (2,'jeans',3);
INSERT INTO "products" VALUES (3,'shoes',2);
INSERT INTO "products" VALUES (4,'shirt',NULL);
INSERT INTO "brands" VALUES (1,'acme');
INSERT INTO "brands" VALUES (2,'wonka');
INSERT INTO "brands" VALUES (3,'gekko');
INSERT INTO "brands" VALUES (4,'stark');
PRAGMA foreign_keys = ON;
COMMIT;
这是查询产品时我的API端点应产生的示例JSON输出：
{
"id": 1,
"name": "t-shirt",
"brand": "acme",
"options": {
"color": ["black", "white"],
"size": ["9"]
}
}
到目前为止，我已经提出了查询的第一部分，但是我无法弄清楚如何输出选项：
SELECT DISTINCT
products.id,
products.name,
brands.name AS brand,
product_options.value AS option_value
FROM products
INNER JOIN brands on brands.id = products.brand_id
INNER JOIN product_options on product_options.product_id = products.id
WHERE products.id = 1
这将返回以下结果：
id name brand option_value
1 t-shirt acme black
1 t-shirt acme white
1 t-shirt acme 9
但是，我想要的输出是：
id name brand color size
1 t-shirt acme black, white 9
当然，查询必须是“动态的”，因此将来也应返回添加到产品中的任何其他选项，例如：
id name brand color size condition
1 t-shirt acme black, white 9 new, used
任何想法我如何实现这一目标？我知道我可以简单地创建两个端点，一个端点检索产品列表，另一个端点返回其选项，但是将所有端点合并在一个文档中似乎是一个更好的解决方案。
任何帮助将不胜感激，谢谢！
回答如下：您可以使用标准的JSON1模块和一堆聚合子查询来直接从sqlite生成JSON：
SELECT
json_object('id', p.id
, 'name', p.name
, 'brand', b.name
, 'options', (SELECT json_group_object(name, ja)
FROM (SELECT po.name
, json_group_array(po.value) AS ja
FROM product_options AS po
WHERE po.product_id = p.id
GROUP BY po.name)))
FROM products AS p
JOIN brands AS b ON p.brand_id = b.id
WHERE p.id = 1;
{"id":1,"name":"t-shirt","brand":"acme","options":{"color":["black","white"],"size":["9"]}}
为了获得最佳结果，请在product_options(product_id, name)上添加索引。