#arrays #json #sql-server
#массивы #json #sql-сервер
Вопрос:
Мне поручено импортировать данные в SQL, которые в значительной степени являются JSON, но не совсем. Я использовал OPENROWSET / OPENJSON для импорта в промежуточную таблицу, и данные выглядят следующим образом
Чего мне нужно добиться, так это перенести это в одну таблицу со следующей структурой
У меня ничего не получается, я даже пытаюсь обновить данные в промежуточной таблице, чтобы они выглядели так, и импортировать, но без радости.
Моя текущая попытка:
SELECT A.[DATE], A.[VALUE]
FROM OPENJSON(@JSON) AS I
CROSS APPLY (
SELECT *
FROM OPENJSON (@JSON) WITH (
[DATE] NVARCHAR(MAX) '$.DATE',
[VALUE] NVARCHAR(MAX) '$.VALUE'
)
) A OUTPUT
Какие-либо рекомендации?
Комментарии:
1. Посмотрите на
OPENJSON
.2. Какова ваша нынешняя попытка? Текст в
array
столбце является допустимым JSON, и использованиеOPENJSON
и соответствующие определения столбцов являются рабочим решением.3. Я попытался ВЫБРАТЬ A.[DATE], A.[VALUE] ИЗ OPENJSON (@JSON) ПРИ ПЕРЕКРЕСТНОМ ПРИМЕНЕНИИ (ВЫБЕРИТЕ * ИЗ OPENJSON (@JSON) С ПОМОЩЬЮ ([DATE] NVARCHAR(MAX) ‘$.DATE’, [VALUE] NVARCHAR(MAX) ‘$.VALUE’)) ВЫВОД: но это чисто возвращает нули.
Ответ №1:
Просто используйте этот способ:
CREATE TABLE #tmp (
instance NVARCHAR(50),
json NVARCHAR(1000)
)
INSERT #tmp
VALUES
( N'server1.com',
N'[{"date":10000, "value":"6"},{"date":20000, "value":"8"}]'
)
SELECT
t.instance, Date,Value
FROM #tmp t
OUTER APPLY OPENJSON(t.json)
WITH (
Date varchar(200) '$.date' ,
Value VARCHAR(100) '$.value'
)
Ответ №2:
Для вашего первого набора данных у вас есть дважды вложенный массив JSON, поэтому вам нужно сначала использовать OPENJSON
для взлома внешний массив:
SELECT
instance
JSON_VALUE(j1.innerArray, '$[0]') AS date,
JSON_VALUE(j1.innerArray, '$[1]') AS value
FROM table t
CROSS APPLY OPENJSON(t.json) WITH (
innerArray nvarchar(max) '
Для второй версии просто измените JSON_VALUE
параметры:
JSON_VALUE(j1.innerArray, '$.date') AS date,
JSON_VALUE(j1.innerArray, '$.value') AS value
Ответ №3:
Оригинальный ответ:
Причиной неожиданного результата является тот факт, что у вас есть вложенные массивы JSON, но WITH
предложение неверно. Вам нужно использовать соответствующее WITH
предложение, например, приведенное ниже утверждение:
Таблица:
SELECT *
INTO Data
FROM (VALUES
('server1.com', '[[1613347200, "7"], [1613347205, "8"], [1613347202, "9"]]'),
('server2.com', '[[1613317200, "3"], [1613347215, "2"], [1613347212, "1"]]')
) v (instance, array)
Инструкция:
SELECT d.instance, j.[date], j.[value]
FROM Data d
OUTER APPLY OPENJSON(d.array) WITH (
[date] numeric(10, 0) '$[0]',
[value] varchar(1) '$[1]'
) j
Результат:
instance date value
-----------------------------
server1.com 1613347200 7
server1.com 1613347205 8
server1.com 1613347202 9
server2.com 1613317200 3
server2.com 1613347215 2
server2.com 1613347212 1
Обновить:
Ваша вторая попытка почти верна. Причиной NULL
значений является тот факт, что path
часть определений столбцов в WITH
предложении чувствительна к регистру:
SELECT d.instance, j.[date], j.[value]
FROM (VALUES
('server1.com', '[{"date":1613347200, "value":"7"}, {"date":1613347200, "value":"8"}]')
) d (instance, array)
OUTER APPLY OPENJSON(d.array) WITH (
[date] numeric(10, 0) '$.date',
[value] varchar(1) '$.value'
) j
AS JSON
) j1
Для второй версии просто измените JSON_VALUE
параметры:
Ответ №3:
Оригинальный ответ:
Причиной неожиданного результата является тот факт, что у вас есть вложенные массивы JSON, но WITH
предложение неверно. Вам нужно использовать соответствующее WITH
предложение, например, приведенное ниже утверждение:
Таблица:
Инструкция:
Результат:
Обновить:
Ваша вторая попытка почти верна. Причиной NULL
значений является тот факт, что path
часть определений столбцов в WITH
предложении чувствительна к регистру: