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