对于 JSON 路径与对于 JSON 自动 SQL 服务器

我在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"
}
]
}
]

我已经尝试了 json auto 和 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'
,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

对于 JSON 自动创建以下内容(地址部分不是嵌套的(但增量是(：

[
{
"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"
}
]
}
]

对于 JSON 路径创建以下内容(地址部分是嵌套的，但认证不是 - 整个块重复(：

[
{
"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

您需要使用子查询来生成具有值列表的属性。对子查询使用别名，该别名是生成的 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 语法。

相关内容

最新更新

热门标签：