SQL Server 2016 - Массив в отдельные столбцы

#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 предложении чувствительна к регистру: