10

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?

tukan
  • 17,050
  • 1
  • 20
  • 48
Jayesh Tank
  • 129
  • 1
  • 8

3 Answers3

15

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

I would leave out the [ ] from your OPENJSON 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   "')
tukan
  • 17,050
  • 1
  • 20
  • 48
0
JSON_VALUE(c.value,'$.Serial Number') as [Serial Number] is throwing an error with a space. How do I resolve the space in the field name using JSON_VALUE .

by itself '$.Full Name' is not a valid json, but adding '$."Full Name"' the json becomes valid
Golden Lion
  • 3,840
  • 2
  • 26
  • 35
0

I know this says using OPENJSON, but Googling JSON_VALUE led me here. To get the value using JSON_VALUE for a key that has a space, don't do this:

 JSON_VALUE([Data], '$.Your Name') AS [Name]

Put the value in double quotes, like this:

 JSON_VALUE([Data], '$."Your Name"') AS [Name]

and it will work.

duck
  • 747
  • 14
  • 31