时间戳

首页 > 解决方案 > 克服 SQL Server 2016/2019 中 JSON 嵌套数组的通配符限制

arrays - 克服 SQL Server 2016/2019 中 JSON 嵌套数组的通配符限制

问题描述

我很难弄清楚如何绕过通配符限制。

这是我正在研究的一个例子。我有一些要解析的 PTO 请求数据。

每个请求都有一个requestID，每个请求requestID可以有多个paidTimeOffEntries。基本上，如果我休假一周，它会paidTimeOffEntries为 1创造 5 requestID。

我想要做的只是复制通配符 [*] 并paidTimeOffEntries为每个requestID员工拉每个。

这是我的 JSON 和一个SELECT仅提取请求的第一个实例的简单语句

DECLARE @JSON NVARCHAR(MAX) = N'[{"requestStatus":{"code":"approved","labelName":"Approved"},"totalQuantity":null,"totalTime":null,"requests":[{"requestID":"9200017220875_1","requestUri":{"href":"/time/v2/workers/xxxxxxxxxxxxxxxxx/time-off-requests/9200017220875_1"},"requestDesc":null,"requestStartDate":null,"requestStatus":{"code":"approved","labelName":"Approved"},"totalQuantity":{"valueNumber":56,"unitTimeCode":"hour","labelName":"HOURS"},"totalTime":null,"paidTimeOffEntries":[{"timeOffEntryID":null,"paidTimeOffID":"9200017220887_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-09-17","endDateTime":"2020-09-17"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null},{"timeOffEntryID":null,"paidTimeOffID":"9200017220879_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-09-18","endDateTime":"2020-09-18"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null},{"timeOffEntryID":null,"paidTimeOffID":"9200017220877_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-09-21","endDateTime":"2020-09-21"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null},{"timeOffEntryID":null,"paidTimeOffID":"9200017220883_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-09-22","endDateTime":"2020-09-22"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null},{"timeOffEntryID":null,"paidTimeOffID":"9200017220881_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-09-23","endDateTime":"2020-09-23"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null},{"timeOffEntryID":null,"paidTimeOffID":"9200017220885_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-09-24","endDateTime":"2020-09-24"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null},{"timeOffEntryID":null,"paidTimeOffID":"9200017220889_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-09-25","endDateTime":"2020-09-25"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null}],"meta":{"metadataEntitlementCodes":["r","k"],"multiPeriodRequestIndicator":false,"actions":[]},"requestorComment":null,"approvalDueDate":null},{"requestID":"9200022223870_1","requestUri":{"href":"/time/v2/workers/xxxxxxxxxxxxxxxxx/time-off-requests/9200022223870_1"},"requestDesc":null,"requestStartDate":null,"requestStatus":{"code":"approved","labelName":"Approved"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"totalTime":null,"paidTimeOffEntries":[{"timeOffEntryID":null,"paidTimeOffID":"9200022223872_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-10-22","endDateTime":"2020-10-22"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null}],"meta":{"metadataEntitlementCodes":["r","k"],"multiPeriodRequestIndicator":false,"actions":[]},"requestorComment":{"entryDateTime":"2020-10-21","commentTxt":"PTO"},"approvalDueDate":null},{"requestID":"9200022915434_1","requestUri":{"href":"/time/v2/workers/xxxxxxxxxxxxxxxxx/time-off-requests/9200022915434_1"},"requestDesc":null,"requestStartDate":null,"requestStatus":{"code":"approved","labelName":"Approved"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"totalTime":null,"paidTimeOffEntries":[{"timeOffEntryID":null,"paidTimeOffID":"9200022915436_1","paidTimeOffPolicy":{"code":"9200016925931_1","labelName":"PTO - Out of Office"},"entryStatus":{"code":"approved","labelName":"Approved"},"earningType":{"code":"9200016925936_1","labelName":"PTO"},"timePeriod":{"startDateTime":"2020-10-28","endDateTime":"2020-10-28"},"totalQuantity":{"valueNumber":8,"unitTimeCode":"hour","labelName":"HOURS"},"meta":null}],"meta":{"metadataEntitlementCodes":["r","k"],"multiPeriodRequestIndicator":false,"actions":[]},"requestorComment":{"entryDateTime":"2020-10-26","commentTxt":"PTO"},"approvalDueDate":null}]}]';

SELECT a.*
  FROM OPENJSON(@JSON, '$[0].requests[0].paidTimeOffEntries')
      WITH (
                 paidTimeOffID NVARCHAR(25) '$.paidTimeOffID'
                ,totalQuantity DECIMAL(18,2) '$.totalQuantity.valueNumber'
                ,startDateTime DATETIME '$.timePeriod.startDateTime'
                ,endDateTime DATETIME '$.timePeriod.endDateTime'
           ) a

我想要复制的是这个$[*].requests.[*].requestID和这个$[*].requests.[*].paidTimeOffID，这样我就可以得到所有的休假请求，以及他们想要休假的日子

理想情况下，它会返回：

requestID        paidTimeOffID    totalQuantity    startDateTime    endDateTime   
9200017220875_1  9200017220887_1  8                2020-09-17       2020-09-17
9200017220875_1  9200017220879_1  8                2020-09-18       2020-09-18
9200017220875_1  9200017220877_1  8                2020-09-21       2020-09-21
9200017220875_1  9200017220883_1  8                2020-09-22       2020-09-22
9200017220875_1  9200017220881_1  8                2020-09-23       2020-09-23
9200017220875_1  9200017220885_1  8                2020-09-24       2020-09-24
9200017220875_1  9200017220889_1  8                2020-09-25       2020-09-25
9200022223870_1  9200022223872_1  8                2020-10-22       2020-10-22
9200022915434_1  9200022915436_1  8                2020-10-28       2020-10-28

解决这个问题的最佳建议是什么？我希望我忽略了一些简单的事情。我以前做过，但我已经控制了 JSON 的格式。不幸的是，我无法控制这一点。

标签： arraysjsonsql-serversql-server-2016sql-server-2019

解决方案

您可以像这样展平多个openjson()调用：

SELECT x.*
FROM OPENJSON(@JSON, '$') WITH (requests NVARCHAR(MAX) AS JSON) r
CROSS APPLY OPENJSON(r.requests) WITH (paidTimeOffEntries NVARCHAR(MAX) AS JSON) p
CROSS APPLY OPENJSON(p.paidTimeOffEntries) WITH (
    paidTimeOffID NVARCHAR(25)  '$.paidTimeOffID',
    totalQuantity DECIMAL(18,2) '$.totalQuantity.valueNumber',
    startDateTime DATETIME      '$.timePeriod.startDateTime',
    endDateTime   DATETIME      '$.timePeriod.endDateTime'
) x

DB Fiddle 上的演示：

paidTimeOffID | 总量 | 开始日期时间 | 结束日期时间            
:---------------- | ------------: | :------------------------ | :------------------------
9200017220887_1 | 8.00 | 2020-09-17 00:00:00.000 | 2020-09-17 00:00:00.000
9200017220879_1 | 8.00 | 2020-09-18 00:00:00.000 | 2020-09-18 00:00:00.000
9200017220877_1 | 8.00 | 2020-09-21 00:00:00.000 | 2020-09-21 00:00:00.000
9200017220883_1 | 8.00 | 2020-09-22 00:00:00.000 | 2020-09-22 00:00:00.000
9200017220881_1 | 8.00 | 2020-09-23 00:00:00.000 | 2020-09-23 00:00:00.000
9200017220885_1 | 8.00 | 2020-09-24 00:00:00.000 | 2020-09-24 00:00:00.000
9200017220889_1 | 8.00 | 2020-09-25 00:00:00.000 | 2020-09-25 00:00:00.000
9200022223872_1 | 8.00 | 2020-10-22 00:00:00.000 | 2020-10-22 00:00:00.000
9200022915436_1 | 8.00 | 2020-10-28 00:00:00.000 | 2020-10-28 00:00:00.000

推荐阅读