user2025537 user2025537 - 1 year ago 137
JSON Question

How to read field name with space in Json using OPENJSON in SQL Server 2016

How can I read value from json file in that field name contains space using OPENJSON in Sql Server 2016. See the below code:

DECLARE @json NVARCHAR(MAX)
SET @json = N'{ "full name" : "Jayesh Tank"}';
SELECT * FROM OPENJSON(@json) WITH ( [name] [varchar](60) '$.full name')


Also another sample code in that space is after field name.

SET @json = N'{ "name " : "abc"}';
SELECT * FROM OPENJSON(@json) WITH ( [name] [varchar](60) '$.name')


'$.name' will return null.Is there way to read this value?

Answer Source

Generally it is a bad idea to use spaces in the attribute name.

I would leave out the [ ] from your OPENJASON name and varchar(60) - source MSDN OPENJSON.

Now to actually answer your question:

You need to format your attribute with double quotes in the WITH clause:

@DECLARE @json NVARCHAR(MAX);
SET @json=N'{ "full name" : "Jayesh Tank"}';
SELECT * FROM OPENJSON(@json) WITH (name varchar(60) '$."full name"')

for the second one:

SET @json = N'{ "name   " : "abc"}';
SELECT * FROM OPENJSON(@json) WITH ( name varchar(60)'$."name   "')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download