Блог

Я извлек JSON из API (часть файла json была показана внизу). Я надеялся проанализировать json и сохранить его в таблице SQL. При следующем SQL-запросе была возвращена только 1 строка. Как я могу вернуть все строки с заголовками таблиц ИМЯ JobNum Водопроводная канализация ? Я попытался в цикле while использовать переменную для замены [0] после $.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
}
 

Я переместил большую часть пути 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'
        )
 

Во-первых, SINGLE_BLOB должно быть SINGLE_CLOB , если это данные ANSI или UTF-8.

Далее, чтобы разбить массив JSON на отдельные строки, вам нужно OPENJSON без схемы, затем вы OPENJSON снова используете каждую строку, где key столбец содержит индекс и value содержит объект.

Если boards в массиве всегда содержится только один объект, вы можете удалить второй OPENJSON и изменить путь к первому на $.data.boards[0].items

Затем мы можем разбить их и снова сложить в один ряд. column_values

 SELECT
    NAME = JSON_VALUE(item.value, '$.name'),
    cv.JobNum,
    cv.Water,
    cv.Sewer 
FROM OPENJSON(@j, '$.data.boards') boards
CROSS APPLY OPENJSON(boards.value, '$.items') item
CROSS APPLY (
    SELECT
        MIN(CASE WHEN title = 'Job' THEN [text] END) JobNum,
        MIN(CASE WHEN title = 'Water'  THEN [text] END) Water,
        MIN(CASE WHEN title = 'Sewer'  THEN [text] END) Sewer
    FROM OPENJSON(item.value, '$.column_values')
    WITH (
        title varchar(100),
        [text] varchar(100)
    ) column_values
) cv