我在使用 SQL Server 將 JSON 陣列轉換為不同的輸出格式時遇到了挑戰...
輸入：
[
{
"label": "City",
"values": [
"Test City"
]
},
{
"label": "imgTest",
"values": [
{
"identifier": "56696553-48F4-4BC5-BB43-FF4F71743EE9",
"filename": "file1.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
},
{
"identifier": "2D9106D7-71A1-440E-8255-679E8905B32E",
"filename": "file2.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
}
]
}
]
期望的輸出：
[
{
"label": "City",
"answer": "Test City"
},
{
"label": "imgTest",
"answer": {
"filename": "file1.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
}
},
{
"label": "imgTest",
"answer": {
"filename": "file2.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
}
}
]
我發現的挑戰在于字串和物件的組合作為輸出中的“答案”屬性。
請注意，這只是非常嵌套的 JSON 輸入的一小部分。謝謝。臨時表等是完全可能的，因為這很可能通過存盤程序發生
我提到 SQL 的原因是因為這是我最熟悉的技術，并且過去曾成功地用于這些目的。
在影像發揮作用之前，我的初始方法是構建一個具有不同層的“標簽值”對的臨時表，然后將此表轉換為帶有一系列嵌套“for json path”陳述句的 json。
但是，當我使用影像資料執行此操作時，它假定內容是字串，因此它會轉義將輸出發送到下一個應用程式時導致問題的字符...
uj5u.com熱心網友回復：
默認情況下，該FOR JSON子句轉義 JSON 輸出中的特殊字符\，通常您可以通過使用來避免這種情況JSON_QUERY()（如檔案中所述...JSON_QUERY 回傳一個有效的 JSON 片段。因此，FOR JSON 不會轉義特殊字符JSON_QUERY 回傳值中的字符）。
正如您已經知道的，這里的問題是$."values"JSON的一部分包含一個字串和一個物件。此問題的一個可能解決方案是生成一個包含兩列名稱相同的表并應用該FOR JSON AUTO子句。的輸出FOR JSON AUTO由SELECT子句中列的順序和子句中參考的表的組合決定SELECT，所以我不確定這個輸出是“設計使然”還是只是一個有用的副作用。當然，如果您包含該INCLUDE_NULL_VALUES選項，則結果將包含兩列中的值。
JSON：
DECLARE @json nvarchar(max) = N'
[
{
"label": "City",
"values": [
"Test City"
]
},
{
"label": "imgTest",
"values": [
{
"identifier": "56696553-48F4-4BC5-BB43-FF4F71743EE9",
"filename": "file1.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
},
{
"identifier": "2D9106D7-71A1-440E-8255-679E8905B32E",
"filename": "file2.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
}
]
}
]'
T-SQL：
SELECT
[label],
CASE WHEN ISJSON([value]) = 0 THEN [value] END AS [answer],
JSON_QUERY(CASE WHEN ISJSON([value]) = 1 THEN [value] END) AS [answer]
FROM (
SELECT j1.[label], j2.[value]
FROM OPENJSON(@json) WITH (
[label] nvarchar(100) '$.label',
[values] nvarchar(max) '$.values' AS JSON
) j1
OUTER APPLY OPENJSON(j1.[values]) j2
) t
FOR JSON AUTO, INCLUDE_NULL_VALUES
結果：
[
{
"label":"City",
"answer":"Test City"
},
{
"label":"imgTest",
"answer":{
"identifier":"56696553-48F4-4BC5-BB43-FF4F71743EE9",
"filename":"file1.jpg",
"contentType":"image/jpg",
"bytes":"iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
}
},
{
"label":"imgTest",
"answer":{
"identifier":"2D9106D7-71A1-440E-8255-679E8905B32E",
"filename":"file2.jpg",
"contentType":"image/jpg",
"bytes":"iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
}
}
]
uj5u.com熱心網友回復：
嘗試這個。它在 Visual Studio 中進行了測驗
var jArray = JArray.Parse(json);
foreach (var jObject in jArray)
{
var data = new Data { Label = jObject["label"].ToString() };
var result = new List<Data>();
foreach (var jArr in (JArray)jObject["values"])
{
if (jArr.GetType().Name.ToString() == "JValue") data.ShortAnswer = ((JValue)jArr).ToString();
else
{
if (data.Answer == null) data.Answer = new List<Answer>();
data.Answer.Add(jArr.ToObject<Answer>());
}
}
result.Add(data);
}
結果
[
{
"label": "City",
"answer": null,
"shortAnswer": "Test City"
},
{
"label": "imgTest",
"answer": [
{
"filename": "file1.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
},
{
"filename": "file2.jpg",
"contentType": "image/jpg",
"bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
}
],
"shortAnswer": null
}
]
班級
public partial class Data
{
[JsonProperty("label")]
public string Label { get; set; }
[JsonProperty("answer")]
public List<Answer> Answer { get; set; }
[JsonProperty("shortAnswer")]
public string ShortAnswer { get; set; }
}
public partial class Answer
{
[JsonProperty("filename")]
public string Filename { get; set; }
[JsonProperty("contentType")]
public string ContentType { get; set; }
[JsonProperty("bytes")]
public string Bytes { get; set; }
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/376092.html
標籤：数组 json sql-server 数据转换