How to add a JSON value read from a parent JSON in a table column in SQL Server?

Question

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 · Accepted Answer · 2020-03-10 11:47:18Z

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"}

edited Mar 10, 2020 at 11:47

answered Mar 10, 2020 at 11:40

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

3 Comments

Araf Over a year ago

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

Zhorov Over a year ago

@Araf Where are the spaces?

Araf Over a year ago

like { ................. "name":"Josn", "surname": "Smith" .................}, after the brackets (i'm using dots to show space cause stack overflow deletes spaces)

Collectives™ on Stack Overflow

How to add a JSON value read from a parent JSON in a table column in SQL Server?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related