json - FOR JSON PATH 与 FOR JSON AUTO SQL Server

Question

我在 SQL Server 中创建嵌套 JSON 时遇到问题。我正在尝试创建如下所示的输出:

[
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
        "address1": "1 Oak Grove",
        "address2": "London",
        "address3": "UK"
    },
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

我尝试过 FOR JSON AUTO 和 FOR JSON PATH:

SELECT
  d.SCHOOL_WEBSITE AS websiteURL
  ,d.SCHOOL_EMAIL AS email 
 ,d.SCHOOL_TELEPHONE AS phone
 ,d.[Address 1] AS 'address.address1'
 ,d.[Address 2] AS 'address.address2'
 ,d.[Address 3] AS 'address.address3'
 ,accreditations.[IndiUID] as name   
 ,accreditations.Value as value 
 FROM [TESTDB].[dbo].[DataValues] as d,[TESTDB].[dbo].[accreditations] as accreditations
 WHERE d.Code = accreditations.SchoolCode
 FOR JSON AUTO --PATH

FOR JSON AUTO 创建此内容(地址部分不是嵌套的(但认证是嵌套的):

[
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address.address1": "1 Oak Grove",
    "address.address2": "London",
    "address.address3": "UK",
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

FOR JSON PATH 创建此内容(地址部分是嵌套的，但认证不是 - 整个 block 重复):

[
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator1",
    "value": "True"
  },
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator2",
    "value": "False"
  },
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator3",
    "value": "False"
    }
]

我认为它的关键是围绕认证的某种 FOR JSON 子查询，但我在这方面没有取得任何成功。

使用以下内容创建示例数据:

    /****** Object:  Table [dbo].[accreditations]    Script Date: 11/09/2018 22:29:43 ******/
CREATE TABLE [dbo].[accreditations](
    [SchoolCode] [nvarchar](255) NULL,
    [IndiUID] [nvarchar](255) NULL,
    [Value] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DataValues]    Script Date: 11/09/2018 22:29:44 ******/
CREATE TABLE [dbo].[DataValues](
    [Code] [nvarchar](255) NULL,
    [SCHOOL_NAME_FORMAL] [nvarchar](255) NULL,
    [SCHOOL_WEBSITE] [nvarchar](255) NULL,
    [SCHOOL_EMAIL] [nvarchar](255) NULL,
    [SCHOOL_TELEPHONE] [float] NULL,
    [Address 1] [nvarchar](255) NULL,
    [Address 2] [nvarchar](255) NULL,
    [Address 3] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator2', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator3', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator2', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator3', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator2', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator3', N'True')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'ABC', N'test', N'www.test.edu', N'hello@test.edu', 123456798, N'1 Oak Grove', N'London', N'UK')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'DEF', N'something', N'https://something.edu/fulltime', N'hello@something.edu', 987654321, N'23 Tree Road', N'Paris', N'France')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'GHI', N'university', N'http://www.university.ac.uk/', N'hello@university.ac.uk/', 123123123, N'57 Bonsai Lane', N'London', N'UK')
GO

Answer 1

您需要使用子查询来生成具有值列表的属性。使用子查询的别名，该别名是生成的 JSON 对象上的属性名称。

这应该适合你:

SELECT
    d.SCHOOL_WEBSITE AS 'websiteURL',
    d.SCHOOL_EMAIL AS 'email ',
    d.SCHOOL_TELEPHONE AS 'phone',
    d.[Address 1] AS 'address.address1',
    d.[Address 2] AS 'address.address2',
    d.[Address 3] AS 'address.address3',
    (
        SELECT 
            [IndiUID] as 'name',
            Value as 'value' 
        FROM [dbo].accreditations as ac
        WHERE ac.SchoolCode = d.Code
        FOR JSON PATH
    ) AS accreditations
FROM dbo.DataValues d
FOR JSON PATH;

(顺便说一句，您应该停止使用旧的隐式 JOIN 语法。)