存储过程"/>
SQL,JSON数据和存储过程
我正在使用带有TypeScript的Node / Express和SQL数据库PostgreSQL或MySQL开发用于CRUD操作的api。我的目标是使用扎实的原理和简洁的体系结构方法来创建一个与库无关的API,并尽可能在存储过程中使用SQL查询。当然,最后要学习更好的SQL。
我以前使用活动记录框架作为Sequelize(Node)或Django(Python)以及LoopBack;但是现在我想避免它们并控制我的代码库。我有一些在存储过程中编写SQL查询的经验,但是那是很久以前的事情,而且还只是在中间级别。
我的问题与插入/更新应遵循的方法有关。可以说我有帖子和标签,具有多对多关系:这为我们提供了Post
,Tag
和Post_has_Tag
的表格:
CREATE TABLE Post (
id INT NOT NULL,
title VARCHAR(45) NULL,
text LONGTEXT NULL,
PRIMARY KEY (id));
CREATE TABLE Tag (
id INT NOT NULL,
tag VARCHAR(45) NULL,
PRIMARY KEY (id));
CREATE TABLE Post_has_Tag (
Post_id INT NOT NULL,
Tag_id INT NOT NULL,
PRIMARY KEY (Post_id, Tag_id),
INDEX fk_Post_has_Tag_Tag1_idx (Tag_id ASC),
INDEX fk_Post_has_Tag_Post_idx (Post_id ASC),
CONSTRAINT fk_Post_has_Tag_Post
FOREIGN KEY (Post_id)
REFERENCES Post (id),
CONSTRAINT fk_Post_has_Tag_Tag1
FOREIGN KEY (Tag_id)
REFERENCES Tag (id));
而且我可以插入一些数据:
INSERT INTO Post (id, title, text) VALUES (1, 'Post 1', 'Post 1 text');
INSERT INTO Post (id, title, text) VALUES (2, 'Post 2', 'Post 2 text');
INSERT INTO Tag (id, tag) VALUES (1, 'Tag 1');
INSERT INTO Tag (id, tag) VALUES (2, 'Tag 2');
INSERT INTO Post_has_Tag (Post_id, Tag_id) VALUES (1, 1);
INSERT INTO Post_has_Tag (Post_id, Tag_id) VALUES (1, 2);
INSERT INTO Post_has_Tag (Post_id, Tag_id) VALUES (2, 1);
INSERT INTO Post_has_Tag (Post_id, Tag_id) VALUES (2, 2);
现在,从我的API中,我想调用存储过程并检索JSON数据:例如,所有带有标签的帖子的典型选择都应将它们输出到JSON对象中:
SELECT *
FROM Post
INNER JOIN Tag
结果:
[
{
id: 1,
title: "Post 1",
text: "Post body",
tags: [
{
id: 1,
tag: "tag 1",
},
{
id: 2,
tag: "tag 2",
},
],
},
{
id: 2,
title: "Post 2",
text: "Post body",
tags: [
{
id: 1,
tag: "tag 1",
},
{
id: 2,
tag: "tag 2",
},
],
},
];
这对于MySQL> 5.7-How to convert result table to JSON array in MySQL-来说是可能的。
但是如何进行反演,并在存储过程中插入带有嵌套数据的对象数组?
如果可能,我想先尝试使用MySQL,然后再迁移到PostgreSQL;除非只有PostgreSQL提供该选项。
回答如下:在Postgres 10.0中-
场景1-查询结果到JSON
Postgres内置函数row_to_json
将以JSON格式转换查询的输出行,array_agg
会将其聚合为JSON数组。有问题的输出是2级JSON,因此我们必须重复2次。
必需查询为
select
array_agg(row_to_json(tab)) from
(
SELECT
t1.id,
t1.title,
t1.text1,
array_agg(row_to_json(t2.*)) as Tags
FROM Post t1
inner join Tag t2 on t1.id=t2.id
group by t1.id, t1.title, t1.text1
) tab
DB-Fiddle Example
更多推荐
SQL,JSON数据和存储过程
发布评论