How to insert JSON object to SQL Server 2016 as nvarchar

Question

I want to insert a few JSON objects into my SQL Server 2016 table.

My table structure is as follows:

field           type            nullable   default
name          | nvarchar(max) | true     | Null
age           | nvarchar(max) | true     | Null
homeAddress   | nvarchar(max) | true     | Null
officeAddress | nvarchar(max) | true     | Null

I am using the following query to insert my data:

DECLARE @json NVARCHAR(MAX) = N'{"name":"John Smith","age":32,"homeAddress":{"addr1":"123rd West Street, Willow Apt","addr2":"#55 Suite","zipCode":12345,"city":"Austin","state":"TX"},"officeAddress":{"addr1":"23rd West Street","addr2":"","zipCode":12345,"city":"Austin","state":"TX"}}';

INSERT INTO Employee 
    SELECT * 
    FROM OPENJSON(@json)
    WITH (name nvarchar(50), 
          age int, 
          homeAddress nvarchar(max), 
          officeAddress nvarchar(max)
         );

However, only the value of name and age are populated in the table. Both homeAddress and officeAddress values are NULL.

What is wrong with my query? How can I insert a JSON object as nvarchar?

Seems like both `homeAddress` as well as `officeAddress` aren't just plain strings - but JSON objects themselves ... — marc_s, Sep 20 '17 at 13:56
Yes homeAddress and officeAddress are both json objects. I'm looking for tutorial to convert the objects to string, but still no luck. — KnightCavalry, Sep 20 '17 at 14:20

score 8 · Accepted Answer · answered Sep 20 '17 at 15:06

I'll admit I have no experience with JSON stuff in SQL Server 2016, but looking at this tutorial:

https://learn.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server

It seems like adding AS JSON after your NVARCHAR(max) columns may be what you need:

INSERT INTO Employee 
    SELECT * 
    FROM OPENJSON(@json)
    WITH (name nvarchar(50), 
          age int, 
          homeAddress nvarchar(max) AS JSON, 
          officeAddress nvarchar(max) AS JSON
         );

This is perfect. Thank you! – KnightCavalry Sep 25 '17 at 05:40 — KnightCavalry, Sep 25 '17 at 05:40

How to insert JSON object to SQL Server 2016 as nvarchar

1 Answers1

Linked