Select Json string with Select

Question

I have following statement that I am trying to execute in Sql Server 2016 CTP 3:

DECLARE @json nvarchar(max)

set @json = '[ 
   { "name": "John" },
   { "name": "Jane", "surname": "Doe" }
]'

select
    'othervalue' as o,
    @json as j
for json path

The problem is when I execute these statements I get the following Json string (with escaped characters):

[{"o":"othervalue","j":"[ \r\n   { \"name\": \"John\" },\r\n   { \"name\": \"Jane\", \"surname\": \"Doe\" }\r\n]"}]

My question is how can select a Json string with a select statement correctly (without escaped characters).

Thanks

np_6 · Accepted Answer · 2021-04-25 19:45:31Z

2

Wrap @json variable with JSON_QUERY:

select
     'othervalue' as o,
      JSON_QUERY(@json) as j
for json path

See frequently asked questions on MSDN https://msdn.microsoft.com/en-us/library/mt631706.aspx

edited Apr 25, 2021 at 19:45

np_6

5081 gold badge7 silver badges20 bronze badges

answered Feb 28, 2016 at 19:52

Jovan MSFT

14.8k4 gold badges44 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

Select Json string with Select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related