CODEWITHSUNDEEP
jsonnestedspecial-characterssql-server-2016
Reza Akraminejad
Reza Akraminejad

Reputation: 1499

SQL Server parse json value with special character in nested properties

I have a json value like this and want to query it with SQL Server:

declare @buffer nvarchar(max)={"request": {"user-agent": "Mozilla/5.0" } }

I searched and found that, for special values in json properties we can use ""

but it won't work for second and nested levels like user-agent in here

select json_query(@buffer,'$.request."user-agent"')

It works if json value like this:

select json_query(@buffer,'$."req-uest"')

but not in this level:

declare @buffer nvarchar(max)='{"request": {"user-agent": "Mozilla/5.0" } }'

select json_query(@buffer,'$.request."user-agent"')

Upvotes: 1

Views: 1253

Answers (1)

Ed Harper
Ed Harper

Reputation: 21505

Because you are returning a single value rather than an array or object, you should use JSON_VALUE instead of JSON_QUERY:

select json_value(@buffer,'$.request."user-agent"')

Upvotes: 4

Related Questions