SELECT varchar column with JSON data as JSON

Question

I have a table with couple columns and one column (let's call it EXIF) is varchar and contains a valid JSON string.

I want to select the table with FOR JSON and I want to include the JSON data from the EXIF column as a part of the JSON result.

Now the data in the EXIF column is returned as string and escaped in the result.

SELECT Col1, EXIF 
  FROM blah 
   FOR JSON AUTO

Expected:

{
  "Col1": "something",
  "EXIF": {
    "GPS": "xxx"
  }
}

What I get instead

{
  "Col1": "something",
  "EXIF": "{\"GPS\": \"xxx\"}"
}

demo

Answer 1

Already solved. The key was to put the JSON_QUERY outside the CASE.

not working

SELECT Col1,
CASE WHEN ISJSON(EXIF)=1 THEN JSON_QUERY(EXIF) END AS EXIF2
FROM BLAH
FOR JSON AUTO

worked

SELECT Col1,
JSON_QUERY(CASE WHEN ISJSON(EXIF)=1 THEN EXIF END) AS EXIF2
FROM BLAH
FOR JSON AUTO