时间戳

首页 > 解决方案 > 如何在三个表之间请求 JSON

sql - 如何在三个表之间请求 JSON

问题描述

假设我有 3 张桌子employee，child并且employee_child.

第一个表包含idandname列，第二个包含idand name，最后一个包含id, employee_id, child_id。

这些表 (employee和child) 在表中关联employee_child。

为了提供帮助，我让这些查询填充这些表：

CREATE TABLE employee (
id INTEGER PRIMARY KEY,
name VARCHAR(128))

INSERT INTO employee(id, name)
VALUES(1, 'Josh'), (2, 'Michel'), (3, 'Will')

CREATE TABLE child (
id INTEGER PRIMARY KEY,
name VARCHAR(128))

INSERT INTO child(id, name)
VALUES(1, 'Karen'), (2, 'Adam'), (3, 'Ellen')

CREATE TABLE employee_child (
id INTEGER PRIMARY KEY,
employee_id INTEGER NOT NULL,
child_id INTEGER NOT NULL)

ALTER TABLE employee_child ADD FOREIGN KEY (employee_id) REFERENCES employee (id)
ALTER TABLE employee_child ADD FOREIGN KEY (child_id) REFERENCES child (id)

INSERT INTO employee_child(id, employee_id, child_id)
VALUES(1, 1, 1), (2, 1, 2), (3, 2, 3)

从表中选择的结果

我需要使用 JSON 请求来获取员工的姓名和他的孩子的姓名employee.id，并得到以下答案：

{
   "id":1,
   "name":"Josh",
   "children":[
      {
         "id":1,
         "name":"Karen"
      },
      {
         "id":2,
         "name":"Adam"
      }
   ]
}

我尝试了另一种方法，但我得到的最正确答案是以下查询：

SELECT [''] = (SELECT e.id, e.name
               FROM employee e
               WHERE e.id = 1
               FOR JSON PATH), 
       [children] = (SELECT c.id, c.name
                     FROM child c
                     INNER JOIN employee_child ec ON ec.child_id= c.id
                     WHERE ec.employee_id = 1
                     FOR JSON PATH)
FOR JSON PATH

我想获得支持以实现完整的答案。

标签： sqljsonsql-server

解决方案

FOR JSON AUTO似乎可以让你得到你想要的：

SELECT e.id,
       e.name,
       children.id,
       children.name
FROM dbo.employee e
     JOIN dbo.employee_child ec ON e.id = ec.employee_id
     JOIN dbo.child children ON ec.child_id = children.id
WHERE e.id = 1
FOR JSON AUTO;

推荐阅读