How to get unescaped value from JSON PATH

Question

Basing my question on the topic in the link: How to get not escaped value from json

This is the query answer given from the link above to get the result: [{"disk":{"name":"/dev/sdb"}}]

How can I get the value in lookupvalue column if I want the output to look like this? [{"disk":"/dev/sdb","scope":"common"}]

Answer 1

use in sql-server

SELECT disk,scope
FROM tblDataInfo
FOR JSON PATH;

demo in db<>fiddle

in mysql

SELECT JSON_ARRAYAGG(JSON_OBJECT('disk', disk, 'scope',scope)) 
from tblDataInfo

Answer 2

The escape characters are needed to properly deserialize the string. To display the field values without escape characters deserialize the JSON. Something like this

declare @json            nvarchar(max)=N'[{"disk":{"name":"\/dev\/sdb"}}]';

select json_value(disk, N'$.name') [name] 
from openjson(json_query(@json, N'$[0]'))
        with (disk        nvarchar(max) as json);

There are many different ways to open the JSON

select json_value(json_query(@json, N'$[0]'), N'$.disk.name') [name];

name
/dev/sdb

Answer 3

SQL Server by default escapes \ character. The only way to prevent this is to generate your object manually.

In this case, you will have to generate the whole object yourself

SELECT CONCAT(
    '[{"disk":"',
    REPLACE(disk, '\', '\\'),
    '","scope":"',
    REPLACE(scope, '\', '\\'),
    '"}]')
FROM (VALUES
    ('/dev/sdb', 'common')
) AS t(disk, scope);

JSON_QUERY is used in that question only to prevent escaping again in the outer JSON PATH