CODEWITHSUNDEEP
sqljsonsql-serverjsondb
Araf
Araf

Reputation: 273

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

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

Upvotes: 1

Views: 654

Answers (1)

Zhorov
Zhorov

Reputation: 29993

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

Upvotes: 2

Related Questions