SQL Server语言 - FOR JSON PATH

Question

我知道我需要使用FOR JSON来实现这一点，我只是不确定如何按特定列进行分组。

我有以下数据集：

property confirmation exemptions                                taxReasonId                 taxId
0145     29SW8TW9F    2020-05-09: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     2QWKNZM8F    2020-08-07: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     2QWKNZM8F    2020-08-08: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-06-25: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-06-26: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-06-27: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-06-28: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-06-29: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid    
0145     3BM6SV9C3    2020-06-30: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-07-01: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-07-02: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-07-03: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3BM6SV9C3    2020-07-04: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-13: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-14: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-15: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-16: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-17: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-18: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-19: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-20: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-21: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-22: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-23: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-24: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-25: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid
0145     3FY4HQ7RB    2020-05-26: ["occupancy tax","room tax"]  5e61836ed0687c000143d77e    OTA Prepaid

我需要将每组"确认"转换为JSON有效载荷，最好带有一个"确认"的标识符列：

{
"taxId": "OTAPrepaid",
"taxReasonId": "5e61836ed0687c000143d77e",
"exemptions":
{
"2020-08-07": ["occupancy tax", "room tax"],
"2020-08-08": ["occupancy tax", "room tax"]
}
}

这是我用来获取数据集的查询：

SELECT  res.property, res.confirmationId, CAST(date AS VARCHAR(12)) + ': ' + tax.taxClass AS exemptions, id AS taxReasonId, tax.reasonName AS taxId
FROM
(
SELECT  property, date, confirmationId
FROM dbo.tb_rguest_reservation_records
WHERE CAST(property AS INT) IN
(
50,66,74,98,105,149,            --Kentucky, Room Tax Exempt
132,                            --South Carolina, Room and Occupancy Tax Exempt
11,160,165,167,             --Colorado, Room Tax Exempt
19,46,87,88,145,169,191,        --Indiana, Room and Occupancy Tax Exempt
116,142,162                 --New Mexico, Room Tax Exempt
)
AND ratePlanId IN ('NOPTS','ADVOTC','ADVPLA','ADVHT','OTAPLA','OTAPAO','OTAHT','OTAHOT')
AND arrivalDate >= CAST(GETDATE() AS DATE)
AND property = '0145'
) res
LEFT JOIN
(
SELECT property, reasonName, id, taxClass
FROM dbo.tb_rguest_tax_exempt_reasons
WHERE reasonName = 'OTA Prepaid'
) tax
ON res.property = tax.property
FOR JSON PATH

当我运行FOR JSON路径时，它会给我以下信息，而这并不是我想要的。

[
{
"property": "0145",
"confirmationId": "74XD3NMWF",
"exemptions": "2020-03-06: ["occupancy tax","room tax"]",
"taxReasonId": "5e61836ed0687c000143d77e",
"taxId": "OTA Prepaid"
},
{
"property": "0145",
"confirmationId": "T6JYFMT6P",
"exemptions": "2020-03-06: ["occupancy tax","room tax"]",
"taxReasonId": "5e61836ed0687c000143d77e",
"taxId": "OTA Prepaid"
},
{
"property": "0145",
"confirmationId": "92DR3M7DB",
"exemptions": "2020-03-11: ["occupancy tax","room tax"]",
"taxReasonId": "5e61836ed0687c000143d77e",
"taxId": "OTA Prepaid"
},

等等。。。

Answer 1

我手头没有SQL Server 2016的实例，但在SQL Server 2017上，此查询似乎给出了您想要的结果：

SELECT confirmation,
MAX(taxId)       as taxId,
MAX(taxReasonId) as taxReasonId,
JSON_QUERY('{' +
STRING_AGG('"' + SUBSTRING(exemptions, 1, 10) + '"' +
SUBSTRING(exemptions, 11, 100),
', ') +
'}')  as exemptions
FROM tb_rguest_reservation_records t
GROUP BY confirmation FOR JSON PATH

这是一把小提琴，下面是一个结果的例子：

[
{
"confirmation": "29SW8TW9F",
"exemptions": {
"2020-05-09": [
"occupancy tax",
"room tax"
]
},
"taxId": "OTA Prepaid",
"taxReasonId": "5e61836ed0687c000143d77e"
},
{
"confirmation": "2QWKNZM8F",
"exemptions": {
"2020-08-07": [
"occupancy tax",
"room tax"
],
"2020-08-08": [
"occupancy tax",
"room tax"
]
},
"taxId": "OTA Prepaid",
"taxReasonId": "5e61836ed0687c000143d77e"
},
...
]

编辑

以下是相同的查询，没有STRING_AGG用于向后兼容性：

SELECT confirmation,
MAX(taxId)       as taxId,
MAX(taxReasonId) as taxReasonId,
JSON_QUERY('{' + 
STUFF((SELECT ',' + '"' + SUBSTRING(exemptions,1,10) + 
'"' + SUBSTRING(exemptions, 11, 100)
FROM tb_rguest_reservation_records t1
WHERE t1.confirmation = t2.confirmation
FOR XML PATH('')), 1, 1, '')
+ '}')         as exemptions
FROM tb_rguest_reservation_records t2
GROUP BY confirmation
FOR JSON PATH

Answer 2

所以我觉得这是一个丑陋的逻辑，但它有效。。。

IF OBJECT_ID('tempdb.dbo.##temp', 'U') IS NOT NULL DROP TABLE ##temp;
IF OBJECT_ID('tempdb.dbo.##temp2', 'U') IS NOT NULL DROP TABLE ##temp2;
DECLARE @query AS NVARCHAR(MAX)
DECLARE @jsonValue AS NVARCHAR(MAX)
DECLARE @dateChange DATE
DECLARE @property VARCHAR(4)
SET @property = '0145'
DECLARE @confirmation VARCHAR(12)
SET @confirmation = 'C3Q28J82B'
DECLARE @taxClass VARCHAR(100)
SET @taxClass =
(
SELECT taxClass
FROM dbo.tb_rguest_tax_exempt_reasons
WHERE property = @property
AND reasonName = 'OTA Prepaid'
)
DECLARE @dateColumns AS NVARCHAR(MAX)
SET @dateColumns =
STUFF(
(
SELECT DISTINCT ',' + QUOTENAME(date)
FROM dbo.tb_rguest_reservation_records
WHERE confirmationId = @confirmation
AND property = @property
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
DECLARE @dateCount INT
SET @dateCount =
(
SELECT COUNT(*)
FROM dbo.tb_rguest_reservation_records
WHERE confirmationId = @confirmation
AND property = @property
)
DECLARE @dateCounter INT
SET @dateCounter = 1
SET @query =  
'SELECT *
INTO ##temp
FROM
(
SELECT res.confirmationId, tax.reasonName AS taxId, tax.id AS taxReasonId, 
res.date, JSON_QUERY(tax.taxClass) AS taxClass
FROM dbo.tb_rguest_reservation_records res
LEFT JOIN dbo.tb_rguest_tax_exempt_reasons tax
ON res.property = tax.property
WHERE res.confirmationId = ' + '''' + @confirmation + '''' + '
) AS res
PIVOT
(
MIN(res.taxClass)
FOR [date] IN ('+ @dateColumns +')
) AS pvt'
EXEC (@query)
SET @query =
'DECLARE @jsonValue VARCHAR(MAX)
SET @jsonValue =
(
SELECT  taxId,
taxreasonId,
' +
REPLACE(
REPLACE(
REPLACE(
@dateColumns, '[', 'JSON_QUERY(['
), ']', '])'
), ',', ' AS [exemptions.1], ') + ' AS [exemptions.2]' + '
FROM ##temp
FOR JSON PATH
)
SELECT @jsonValue AS jsonValue
INTO ##temp2'
EXEC (@query)
SET @jsonValue =
(
SELECT jsonValue
FROM ##temp2
)
WHILE @dateCounter <= @dateCount
BEGIN
SET @dateChange =
(
SELECT date
FROM
(
SELECT property, confirmationId, date, ROW_NUMBER() OVER(ORDER BY date) AS rowNumber
FROM dbo.tb_rguest_reservation_records
WHERE confirmationId = @confirmation
AND property = @property
) res
WHERE rowNumber = @dateCounter
)
SET @jsonValue =REPLACE(@jsonValue, '"' + CAST(@dateCounter AS VARCHAR(12)) + '"', '"' + CAST(@dateChange AS VARCHAR(12)) + '"')
SET @dateCounter = @dateCounter + 1
END
SELECT @jsonValue