Issue using JSON_MODIFY with JSON_QUERY in a CASE statement

Question

Here's the issue. I have a column in my database (type nvarchar(max)) that I am storing JSON in. I am storing both plain strings or objects in that column like the following:

     JsonTable
|--|-------------------|
|Id|JsonValue          |
|--|-------------------|
|0 |{"sample":"object"}|
|--|-------------------|
|1 |"plain-string"     |
|--|-------------------|

I am trying to use JSON_MODIFY to merge these values with another table's values.

The following works fine for just objects, but not strings:

SELECT JSON_MODIFY('{}',  '$.Values', JSON_QUERY(JsonValue))
FROM JsonTable
WHERE Id = 0 -- Fails when string is included in results

-- Result = |------------------------------|
            |{"Values":{"sample":"object"} |
            |------------------------------|

However it fails to parse the ordinary string (understandably since it is not JSON) So then my solution was to add a case statement to handle strings. However this does not work as wrapping it in a CASE statement string escapes the JSON_QUERY object and garbles it up in the final JSON_MODIFY result.

The following does not work as expected:

SELECT JSON_MODIFY('{}',  '$.Values',
    CASE
      WHEN ISJSON(JsonValue) > 0 THEN JSON_QUERY(JsonValue)
      ELSE REPLACE(JsonValue, '"','')
    END)
FROM JsonTable

-- Result = |-------------------------------------|
            |{"Values":"{\"sample\"::\"object\"}" |
            |-------------------------------------|
            |{"Values":"plain-string"             |
            |-------------------------------------|

JD Francis · Accepted Answer · 2018-07-12 16:26:48Z

2

So I was unable to figure out really why wrapping JSON_QUERY in a CASE statement doesnt return properly, but instead I started using this workaround which is a bit verbose and messy but it works perfectly fine:

SELECT
    CASE
      WHEN ISJSON(JsonValue) > 0
      THEN
        (SELECT JSON_MODIFY('{}',  '$.Values', JSON_QUERY(JsonValue)))
      ELSE
        (SELECT JSON_MODIFY('{}',  '$.Values', REPLACE(JsonValue, '"','')))
    END
FROM JsonTable

-- Result = |-------------------------------------|
            |{"Values":{"sample":"object"}        |
            |-------------------------------------|
            |{"Values":"plain-string"             |
            |-------------------------------------|

answered Jul 12, 2018 at 16:26

JD Francis

5041 gold badge5 silver badges25 bronze badges

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

Comments

JohnLBevan · Accepted Answer · 2018-07-11 22:31:48Z

-1

Have you tried using the string_escape function to format your string for JSON; i.e. assuming your issue is related to the correctly escaping the quotes? http://sqlfiddle.com/#!18/9eecb/24391

SELECT JSON_MODIFY('{}',  '$.Values', 
  case 
    when ISJSON(JsonValue) = 1 then JSON_QUERY(JsonValue)
    else STRING_ESCAPE(JsonValue,'json')
  end
)
FROM (
  values
   (0, '{"sample":"object"}')
  ,(1, 'plain-string')
  ,(2, '"plain-string2"')
) JsonTable(Id,JsonValue)

STRING_ESCAPE documentation

edited Jul 11, 2018 at 22:31

answered Jul 11, 2018 at 22:23

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

1 Comment

JD Francis Over a year ago

That does the opposite of what I need, that adds even more escape characters, I'm trying to have none. Querying that string entry with string_escape would give me {"Values":"\\\"plain-string\\\""}. Additionally the string isnt the issue, its the object.

Collectives™ on Stack Overflow

Issue using JSON_MODIFY with JSON_QUERY in a CASE statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related