在SQLServer中從JSON中提取文本

我正在嘗試從 SQL Server 的 JSON 列中提取特定值。不幸的是，我已經閱讀了幾篇關于這個主題的帖子，但仍然無法弄清楚如何將他們的解決方案轉化為我所需要的。我想提取“foo 測驗”，但根本不明白如何使用嵌套的 JSON 來解決這個問題。有人可以建議嗎？

JSON 列的結構為：

{
    "values": [
        {
            "id": "x01",
            "status": "STATUS1",
            "subStatus": "SubStatus1",
            "values": [
                {
                    "key": "dropdown",
                    "value": "",
                    "optionType": null
                }
            ]
        },
        ...
        {
            "id": "x03",
            "status": "STATUS3",
            "subStatus": "SubStatus3",
            "values": [
                {
                    "key": "dropdown",
                    "value": "",
                    "optionType": null
                },
                {
                    "key": "textInput",
                    "value": null,
                    "optionType": null
                },
                {
                    "key": "checkbox1",
                    "value": true,
                    "optionType": null
                },
                {
                    "key": "textInput2",
                    "value": "foo testing",
                    "optionType": null
                }
            ]
        }
    ]
}

uj5u.com熱心網友回復：

該陳述句取決于決議的 JSON 的結構，在您的情況下，您需要使用兩個嵌套OPENJSON()呼叫和附加APPLY運算子。請注意，您需要AS JSON在"values"列定義中使用來指定參考的屬性包含一個內部 JSON 陣列，并且該列的型別必須是nvarchar(max).

測驗表：

DECLARE @json varchar(max) = '
{
    "values": [
        {
            "id": "x01",
            "status": "STATUS1",
            "subStatus": "SubStatus1",
            "values": [
                {"key": "dropdown", "value": "", "optionType": null}
            ]
        },
        {
            "id": "x03",
            "status": "STATUS3",
            "subStatus": "SubStatus3",
            "values": [
                {"key": "dropdown", "value": "", "optionType": null},
                {"key": "textInput", "value": null, "optionType": null},
                {"key": "checkbox1", "value": true, "optionType": null},
                {"key": "textInput2", "value": "foo testing", "optionType": null}
            ]
        }
    ]
}
'
SELECT JsonColumn
INTO JsonTable
FROM (VALUES (@json)) v (JsonColumn)

陳述：

SELECT j1.[id], j2.[key], j2.[value] -- or add all columns
FROM JsonTable t
CROSS APPLY OPENJSON(t.JsonColumn, '$.values') WITH (
   [id] varchar(3) '$.id',
   [status] varchar(30) '$.status',
   [subStatus] varchar(30) '$.subStatus',
   [values] nvarchar(max) '$.values' AS JSON
) j1
CROSS APPLY OPENJSON(j1.[values], '$') WITH (
   [key] varchar(50) '$.key',
   [value] varchar(50) '$.value',
   [optionType] varchar(50) '$.optionType'
) j2

結果：

id  key        value
---------------------------
x01 dropdown   
x03 dropdown   
x03 textInput  
x03 checkbox1  true
x03 textInput2 foo testing

uj5u.com熱心網友回復：

您可以使用以下查詢

;with summery as(
   SELECT * 
   FROM OPENJSON((SELECT value FROM OPENJSON(@json)))
   WITH (
    id NVARCHAR(50) 'strict $.id',
    status NVARCHAR(50) '$.status',
    subStatus NVARCHAR(50) '$.subStatus',
    [values] NVARCHAR(max) '$.values' AS JSON
  )
) 
select id,status,subStatus,[key],value,optionType from summery 
CROSS APPLY OPENJSON(summery.[values])
   WITH (
      [key] NVARCHAR(50) '$.key',
      [value] NVARCHAR(50) '$.value',
      [optionType] NVARCHAR(50) '$.optionType'
);

db<>fiddle 中的演示

標籤：sql json sql-server

上一篇：僅序列化django序列化器類中查詢集中的某些欄位

下一篇：如何在使用NodeJS寫入JSON時忽略空值或空白值