如何将JSON对象作为nvarchar插入SQL Server 2016 [英] How to insert JSON object to SQL Server 2016 as nvarchar

问题描述

我想在我的SQL Server 2016表中插入一些JSON对象.

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

我的表结构如下:

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

但是，表中仅填充name和age的值. homeAddress和officeAddress值均为NULL.

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

我的查询出了什么问题?如何将JSON对象作为nvarchar插入?

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

推荐答案

我承认我没有使用SQL Server 2016中JSON的经验，但是请看一下本教程:

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

您可能需要在NVARCHAR(max)列之后添加AS JSON:

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

这篇关于如何将JSON对象作为nvarchar插入SQL Server 2016的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！