正在努力尋找以下 JSON 問題的答案。我想用逗號分隔值SKU/Quantity將"shipmentItems"陣列中的整個串列顯示到它們各自的列中。我下面的示例只允許我顯示陣列中的第一個 SKU/數量，但是，我的目標是在列中以逗號分隔列出所有內容。
JSON 示例：
{"shipments": [
{
"shipmentId": 100003768,
"orderNumber": "9648219086",
"shipDate": "2021-10-28",
"serviceCode": "ups_ground",
"shipmentItems": [
{
"orderItemId": 1464643208,
"lineItemKey": "10322938560608",
"sku": "SCPXTSS-BAG-06",
"name": "SCOOP-PLATE-06 (1000ml)",
"weight": {
"value": 0,
"units": "ounces",
"WeightUnits": 1
},
"quantity": 1,
"unitPrice": 0,
"taxAmount": null
},
{
"orderItemId": 1464643207,
"lineItemKey": "10322938527840",
"sku": "SCPZRTS-TRAY-01",
"name": "Beef: Tray 3 (Fill 004)<br>",
"weight": {
"value": 60,
"units": "ounces",
"WeightUnits": 1
},
"quantity": 1,
"unitPrice": 102.72,
"taxAmount": null
}
],
"labelData": null,
"formData": null
}
]
}
我正在使用的 SQL 查詢：
Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\XPS-LT\json\today\shipments_20211031.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON, '$.shipments')
WITH
(
[shipmentId] bigint,
[orderNumber] nvarchar(60),
[shipDate] date,
[serviceCode] nvarchar(30),
[sku] nvarchar(MAX) N'$.shipmentItems[0].sku',
[quantity] int N'$.shipmentItems[0].quantity'
)
;
uj5u.com熱心網友回復：
"shipmentItems"輸入 JSON的部分是一個陣列，因此您需要AS JSON在第一個顯式架構中使用一個子句和一個額外的OPENJSON()呼叫：
DECLARE @json nvarchar(max)
...
SELECT
j.[shipmentId], j.[orderNumber], j.[shipDate], j.[serviceCode],
a.[sku], a.[quantity]
FROM OPENJSON (@json, '$.shipments') WITH (
[shipmentId] bigint,
[orderNumber] nvarchar(60),
[shipDate] date,
[serviceCode] nvarchar(30),
[shipmentItems] nvarchar(max) AS JSON
) j
OUTER APPLY (
SELECT
STRING_AGG([sku], ',') WITHIN GROUP (ORDER BY [orderItemId]),
STRING_AGG([quantity], ',') WITHIN GROUP (ORDER BY [orderItemId])
FROM OPENJSON (j.shipmentItems) WITH (
[orderItemId] int '$.orderItemId',
[sku] nvarchar(max) '$.sku',
[quantity] int N'$.quantity'
)
) a ([sku], [quantity])
結果：
shipmentId orderNumber shipDate serviceCode sku quantity
100003768 9648219086 2021-10-28 ups_ground SCPZRTS-TRAY-01,SCPXTSS-BAG-06 1,1
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/343641.html
標籤：sql json sql-server
上一篇：Newtonsoft.JSON在反序列化陣列中被雙引號包圍的物件時窒息
下一篇：反序列化時的映射問題