1

I'm trying to read a JSON and insert the values into a table. Among those values, there is one another JSON object. I'm trying to insert that JSON value into a column, but my code inserts nothing.

Here's what I tried so far:

DROP TABLE IF EXISTS #tmp

DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT [id], [info] 
INTO #tmp
FROM OPENJSON(@json)
     WITH
         (
          [id] NVARCHAR(100), 
          [info] NVARCHAR(max)
         );

SELECT * FROM #tmp

This is the result I get:

id  info
--------------
2   NULL
5   NULL
Zhorov
  • 28,486
  • 6
  • 27
  • 52
Araf
  • 263
  • 1
  • 5
  • 19

1 Answers1

2

If I understand your problem correctly, you need to use AS JSON option as is mentioned in the documentation:

Use the AS JSON option in a column definition to specify that the referenced property contains an inner JSON object or array. If you specify the AS JSON option, the type of the column must be NVARCHAR(MAX).

Statement:

DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT [id], [info] 
INTO #tmp
FROM OPENJSON(@json) WITH (
  [id] NVARCHAR(100) '$.id', 
  [info] NVARCHAR(max) '$.info' AS JSON
);

Result:

id  info
2   {"name": "John", "surname": "Smith"}
5   {"name": "Jane", "surname": "Smith"}
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • One problem I'm facing is that it's adding spaces for my actual data. Is there a way to remove those spaces? Other than that thanks, it solved the problem – Araf Mar 10 '20 at 11:45
  • @Araf Where are the spaces? – Zhorov Mar 10 '20 at 11:52
  • like { ................. "name":"Josn", "surname": "Smith" .................}, after the brackets (i'm using dots to show space cause stack overflow deletes spaces) – Araf Mar 12 '20 at 05:23