SQL Server parse json value with special character in nested properties

Question

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

Answer 1

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