CODEWITHSUNDEEP
sql-servert-sqldatatables
NamedArray
NamedArray

Reputation: 823

JSON_MODIFY appends variable containing JSON with escape characters instead of JSON string

I have had some success using JSON_MODIFY to append fieldErrors: [] and its content to the root of another object { data: []} so that the resultant object looks something like this: {data: [], fieldErrors: []}.

Problem is, when I append a @variable, it includes a bunch of escape characters.

I expect this: {"name":"PosTitle","status":"the field is messed up, yo"}

BUT, I get this: ["{\"name\":\"PosTitle\",\"status\":\"the field is messed up, yo\"}"]}

    DECLARE
        @fieldErrors nvarchar(max) ='{}'
        ,@jsonResponse nvarchar(max) = '
    {
        "data": [
            {
                "PosTitle": "",
                "PosCode": "86753",
            }
        ]
    }
    '

    --define the fields that are bad
    set @fieldErrors = JSON_MODIFY(JSON_MODIFY(@fieldErrors, '$.name', 'PosTitle'), '$.status', 'the field is messed up, yo')
    print @fieldErrors

    --RESULT, this looks great:
    --{"name":"PosTitle","status":"the field is messed up, yo"}



    -- append fieldErrors to the response

    set @jsonResponse = JSON_MODIFY(@jsonResponse, 'append $.fieldErrors', @fieldErrors)
    print @jsonResponse

    --RESPONSE, this includes escape characters
/*
    {
        "data": [
            {
                "PosTitle": "",
                "PosCode": "86753",

            }
        ]
    ,"fieldErrors":["{\"name\":\"PosTitle\",\"status\":\"the field is messed up, yo\"}"]}


*/

Why are escape characters being added when fieldErrors is appended to the response?

Upvotes: 5

Views: 2573

Answers (1)

NotAnAuthor
NotAnAuthor

Reputation: 1281

Please remove the comma at the end of "PosCode": "86753", as it's not a valid JSON this way.

To answer your question, you are trying to add a string stored in @fieldErrors which results in the escape characters being added.

Instead, set @jsonResponse = JSON_MODIFY(@jsonResponse, '$.fieldErrors', JSON_QUERY(@fieldErrors)) should yield the results you're looking for.

Note that when you use append you are telling JSON_MODIFY that it is adding a value to an array (which may or may not be what you need, but isn't what you wrote you're expecting).

Upvotes: 8

Related Questions