时间戳

首页 > 解决方案 > 如何在 SQL Server 中解析嵌套的 Json

json - 如何在 SQL Server 中解析嵌套的 Json

问题描述

我从 API 中检索了 JSON（json 文件的一部分显示在底部）。我希望解析 json 并存储在 SQL 表中。使用以下 SQL 查询，仅返回 1 行。如何返回所有带有表标题 NAME JobNum Water Sewer 的行？我尝试了 while 循环使用变量替换[0]after $.items，但似乎不起作用。我不确定 json 文件的结构是否适用于交叉应用。

DECLARE @MondayComApi VARCHAR(MAX)

SELECT @MondayComApi = BULKCOLUMN
FROM OPENROWSET(BULK'D:/temp/a.json', SINGLE_BLOB) JSON

IF (ISJSON(@MondayComApi) = 1)
BEGIN
    PRINT 'JSON File is valid';

    SELECT NAME, JobNum, Water, Sewer 
    FROM OPENJSON(@MondayComApi, '$.data.boards')
    WITH (
        NAME VARCHAR(100) '$.items[0].name',
        JobNum VARCHAR(100) '$.items[0].column_values[0].text',
        Water VARCHAR(100) '$.items[0].column_values[1].text',
        Sewer VARCHAR(100) '$.items[0].column_values[2].text'
    )
END
ELSE
BEGIN
    PRINT 'JSON File is invalid';
END

以下是 JSON 的一部分 - 我减少了“项目”的内容以缩短长度：

{
  "data": {
    "boards": [
        {
            "items": [
                {
                    "name": "Holmes Project",
                    "column_values": [
                        {
                            "title": "Job",
                            "text": "D1210"
                        },
                        {
                            "title": "Water",
                            "text": "YES"
                        },
                        {
                            "title": "Sewer",
                            "text": "YES"
                        }
                    ]
                },
                {
                    "name": "Lake Short Project)",
                    "column_values": [
                        {
                            "title": "Job",
                            "text": "D1014"
                        },
                        {
                            "title": "Water",
                            "text": "YES"
                        },
                        {
                            "title": "Sewer",
                            "text": "YES"
                        }
                    ]
                },
                {
                    "name": "Chase Project",
                    "column_values": [
                        {
                            "title": "Job",
                            "text": "D2101"
                        },
                        {
                            "title": "Water",
                            "text": "NO"
                        },
                        {
                            "title": "Sewer",
                            "text": "YES"
                        }
                    ]
                },
                {
                    "name": "Juanita Project",
                    "column_values": [
                        {
                            "title": "Job",
                            "text": "D1102"
                        },
                        {
                            "title": "Water",
                            "text": "YES"
                        },
                        {
                            "title": "Sewer",
                            "text": "YES"
                        }
                    ]
                },
                {
                    "name": "Lowry Project",
                    "column_values": [
                        {
                            "title": "Job",
                            "text": "D1014"
                        },
                        {
                            "title": "Water",
                            "text": "YES"
                        },
                        {
                            "title": "Sewer",
                            "text": "YES"
                        }
                    ]
                }
            ]
        }
    ]
},
"account_id": 5687438790
}

标签： jsonsql-serverapi

解决方案

我将更多的 JSON 路径移出WITH和移入OPENJSON：

SELECT NAME, JobNum, Water, Sewer 
FROM
    OPENJSON(@MondayComApi, '$.data.boards[0].items')
        WITH (
            NAME VARCHAR(100) '$.name',
            JobNum VARCHAR(100) '$.column_values[0].text',
            Water VARCHAR(100) '$.column_values[1].text',
            Sewer VARCHAR(100) '$.column_values[2].text'
        )

推荐阅读