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

Question

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 · Accepted Answer · 2017-07-27 10:42:33Z

16

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   "')

edited Jul 27, 2017 at 10:42

answered Jul 27, 2017 at 9:02

tukan

17.5k1 gold badge25 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2022-10-06 17:00:59Z

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

answered Oct 6, 2022 at 17:00

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

Comments

duck · Accepted Answer · 2023-08-18 16:18:26Z

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.

answered Aug 18, 2023 at 16:18

duck

87717 silver badges32 bronze badges

1 Comment

Diomos Over a year ago

I am getting: Msg 13609, Level 16, State 2, Line 3 JSON text is not properly formatted. Unexpected character 'T' is found at position 0.

alex · Accepted Answer · 2024-03-14 20:22:59Z

If you have a space in the JSON tag, use double quotes inside the single quotes and the JSON tag is case sensitive

 SELECT *  FROM OPENJSON(@json)  WITH (   
        InjectionPoint nvarchar(max) '$."injection Point"',
        tanksize nvarchar(max) '$."tank Size"',
        TargetUsage nvarchar(max) '$."target Usage"',
        TargetAllowance nvarchar(max) '$."target Allowance"',
        PriceGal nvarchar(max) '$."price/Gal"',
        TankLocationId nvarchar(max) '$."tank Location ID"',
        SrAcctMgr nvarchar(max) '$."sr ACCT MGR"',
        District nvarchar(max) '$.district',
        County nvarchar(max) '$.county',
        LiftMechanism nvarchar(max) '$."lift Mechanism"',
        opRegion nvarchar(max) '$.opRegion',
        area nvarchar(max) '$.area',
        u_id nvarchar(max) '$.u_ID'
 
) as JSON

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related