SQL Server 2016 - Array into separate columns

Question

I'm tasked with importing data into SQL thats pretty much JSON but not quite . I've used OPENROWSET/OPENJSON to import into a staging table and the data looks like this

What I need to achieve is migrate that to a single table with the following structure

I'm having no success , I even trying updating the data in the staging table to look like this and import but no joy.

My current attempt:

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

Any recommendations ?

What is your current attempt? The text in the array column is a valid JSON and using OPENJSON and the appropriate columns definitions is a working solution. — Zhorov
– Zhorov, Commented Feb 24, 2021 at 12:46
I had tried 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: but that purely returned NULLs. — Simon Shadowshow
– Simon Shadowshow, Commented Feb 24, 2021 at 12:46

sa-es-ir · Accepted Answer · 2021-02-24 12:58:43Z

1

Just use this way:

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'
              
 )

edited Feb 24, 2021 at 12:58

answered Feb 24, 2021 at 12:48

sa-es-ir

5,1902 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2021-02-24 12:49:44Z

0

For your first set of data, you have a doubly-nested JSON array, so you need to use OPENJSON to break open the outer one first:

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) '$' AS JSON
) j1

For the second version, just change the JSON_VALUE parameters:

    JSON_VALUE(j1.innerArray, '$.date') AS date,
    JSON_VALUE(j1.innerArray, '$.value') AS value

answered Feb 24, 2021 at 12:49

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Comments

Zhorov · Accepted Answer · 2021-02-24 16:58:22Z

Original answer:

The reason for the unexpected result is the fact, that the you have nested JSON arrays, but the WITH clause is not correct. You need to use the appropriate WITH clause, like the statement below:

Table:

SELECT *
INTO Data
FROM (VALUES
   ('server1.com', '[[1613347200, "7"], [1613347205, "8"], [1613347202, "9"]]'),
   ('server2.com', '[[1613317200, "3"], [1613347215, "2"], [1613347212, "1"]]')
) v (instance, array)

Statement:

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

Result:

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

Update:

Your second attempt is almost correct. The reason for the NULL values is the fact, that the path part of the columns definitions in the WITH clause is case sensitive:

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

Collectives™ on Stack Overflow

SQL Server 2016 - Array into separate columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related