FOR JSON PATH returning escaped strings for nested objects

Question

I am trying to write a query to UNION together objects from two related tables and return the Azure SQL Server results as JSON objects. I ran into an issue with the query that I cannot seem to resolve.

When I query the entity table with a sub-object grades list using the FOR JSON PATH option to return JSON-formatted objects, i get the expected result.

Query

SELECT 
    entity.name AS name,
    (
    SELECT
        grade_translation.name,
        grade_translation.short_name
    FROM
        entity_grades,
        grade,
        grade_translation,
        language

    WHERE
         entity.id = entity_grades.entity_id
         AND entity_grades.grade_id = grade.id
         AND grade.id = grade_translation.non_translated_id
         AND grade_translation.language_id = language.id
         AND language.short_name = 'en'

    ORDER BY
        entity_grades.[order]

    FOR JSON PATH
    ) AS grades
FROM entity
LEFT JOIN entity_translation ON entity_translation.non_translated_id = entity.id
LEFT JOIN language ON language.id = entity_translation.language_id
WHERE language.short_name = 'en'
FOR JSON PATH

Result

[{
    "name": "Test Entity 1",
    "about": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Porta lorem mollis aliquam ut porttitor leo. Lacus sed viverra tellus in hac habitasse.Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Integer malesuada nunc vel risus commodo. Neque aliquam vestibulum morbi blandit cursus risus.",
    "grades": [{
        "name": "Grade 9",
        "short_name": "9"
    }, {
        "name": "Grade 10",
        "short_name": "10"
    }, {
        "name": "Grade 11",
        "short_name": "11"
    }, {
        "name": "Grade 12",
        "short_name": "12"
    }]
}, {
    "name": "Test Entity 2",
    "about": "Blah blah blah"
}]

Same goes for the objects that do not have translations.

Query (entities without translations)

SELECT 
    entity.name AS name,
    (
    SELECT
        grade_translation.name,
        grade_translation.short_name
    FROM
        entity_grades,
        grade,
        grade_translation,
        language

    WHERE
         entity.id = entity_grades.entity_id
         AND entity_grades.grade_id = grade.id
         AND grade.id = grade_translation.non_translated_id
         AND grade_translation.language_id = language.id
         AND language.short_name = 'en'

    ORDER BY
        entity_grades.[order]

    FOR JSON PATH
    ) AS grades
FROM entity
LEFT JOIN entity_translation ON entity_translation.non_translated_id = entity.id
LEFT JOIN language ON language.id = entity_translation.language_id
WHERE entity.id NOT IN (SELECT DISTINCT entity_translation.non_translated_id FROM entity_translation)
FOR JSON PATH

Result

[{
    "name": "Test Entity 3"
}]

But when i UNION those two queries together, i get a grades array that is escaped.

Query (UNION)

SELECT 
    entity.name AS name,
    (
    SELECT
        grade_translation.name,
        grade_translation.short_name
    FROM
        entity_grades,
        grade,
        grade_translation,
        language

    WHERE
         entity.id = entity_grades.entity_id
         AND entity_grades.grade_id = grade.id
         AND grade.id = grade_translation.non_translated_id
         AND grade_translation.language_id = language.id
         AND language.short_name = 'en'

    ORDER BY
        entity_grades.[order]

    FOR JSON PATH
    ) AS grades
FROM entity
LEFT JOIN entity_translation ON entity_translation.non_translated_id = entity.id
LEFT JOIN language ON language.id = entity_translation.language_id
WHERE language.short_name = 'en'

UNION

SELECT 
    entity.name AS name,
    (
    SELECT
        grade_translation.name,
        grade_translation.short_name
    FROM
        entity_grades,
        grade,
        grade_translation,
        language

    WHERE
         entity.id = entity_grades.entity_id
         AND entity_grades.grade_id = grade.id
         AND grade.id = grade_translation.non_translated_id
         AND grade_translation.language_id = language.id
         AND language.short_name = 'en'

    ORDER BY
        entity_grades.[order]

    FOR JSON PATH
    ) AS grades
FROM entity
LEFT JOIN entity_translation ON entity_translation.non_translated_id = entity.id
LEFT JOIN language ON language.id = entity_translation.language_id
WHERE entity.id NOT IN (SELECT DISTINCT entity_translation.non_translated_id FROM entity_translation)
FOR JSON PATH

Result

[{
    "name": "Test Entity 1",
    "about": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Porta lorem mollis aliquam ut porttitor leo. Lacus sed viverra tellus in hac habitasse.Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Integer malesuada nunc vel risus commodo. Neque aliquam vestibulum morbi blandit cursus risus.",
    "grades": "[{\"name\":\"Grade 9\",\"short_name\":\"9\"},{\"name\":\"Grade 10\",\"short_name\":\"10\"},{\"name\":\"Grade 11\",\"short_name\":\"11\"},{\"name\":\"Grade 12\",\"short_name\":\"12\"},{\"name\":\"Adult\",\"short_name\":\"AD\"}]"
}, {
    "name": "Test Entity 2",
    "about": "Blah blah blah"
}, {
    "name": "Test Entity 3"
}]

I have tried wrapping the inner SELECT statements with JSON_QUERY as suggested in the following reference, but that had no effect.

Ref: How do I keep FOR JSON PATH from escaping query results?

Query with JSON_QUERY wrapping function

SELECT 
    entity.name AS name,
    JSON_QUERY((
    SELECT
        grade_translation.name,
        grade_translation.short_name
    FROM
        entity_grades,
        grade,
        grade_translation,
        language

    WHERE
         entity.id = entity_grades.entity_id
         AND entity_grades.grade_id = grade.id
         AND grade.id = grade_translation.non_translated_id
         AND grade_translation.language_id = language.id
         AND language.short_name = 'en'

    ORDER BY
        entity_grades.[order]

    FOR JSON PATH
    )) AS grades
FROM entity
LEFT JOIN entity_translation ON entity_translation.non_translated_id = entity.id
LEFT JOIN language ON language.id = entity_translation.language_id
WHERE language.short_name = 'en'

UNION

SELECT 
    entity.name AS name,
    JSON_QUERY((
    SELECT
        grade_translation.name,
        grade_translation.short_name
    FROM
        entity_grades,
        grade,
        grade_translation,
        language

    WHERE
         entity.id = entity_grades.entity_id
         AND entity_grades.grade_id = grade.id
         AND grade.id = grade_translation.non_translated_id
         AND grade_translation.language_id = language.id
         AND language.short_name = 'en'

    ORDER BY
        entity_grades.[order]

    FOR JSON PATH
    )) AS grades
FROM entity
LEFT JOIN entity_translation ON entity_translation.non_translated_id = entity.id
LEFT JOIN language ON language.id = entity_translation.language_id
WHERE entity.id NOT IN (SELECT DISTINCT entity_translation.non_translated_id FROM entity_translation)
FOR JSON PATH

Is there anything else i can try to return proper JSON from this style of query? Can someone explain whether this is a feature or a bug?

Just a thought but have you tried removing "FOR JSON PATH" from the inner queries? When you union the tables i think the JSON is getting formatted twice. — BillRuhl, Apr 01 '19 at 21:50
@BillRuhl The inner `FOR JSON PATH` statements are required because those subqueries need to return single values. — Nicholas Tulach, Apr 02 '19 at 13:10
Did you a least try what I suggested? You may have to tweak the query to get the result set you want however I do believe the output you posted is the result of the JSON array being formatted once when you union the two queries then again on the final SELECT. Remove the formatting on the inner queries then test, if the formatting problem goes away then there's your answer. — BillRuhl, Apr 02 '19 at 16:28
@BillRuhl I think you may be reading the query incorrectly. There is no "final SELECT"; there are two selects and a UNION of the two. — Nicholas Tulach, Apr 02 '19 at 20:34
@Nicolas OK..I could have worded my previous comment differently, the point is I have run into situations when strings are formatted as JSON then when another piece of code re-formats the JSON the quotes get escaped. If you don't want to work on the queries the just remove the "\" with REPLACE(myJson, '\', '') — BillRuhl, Apr 03 '19 at 14:02
@BillRuhl It's actually not quite as simple as replacing the backslashes because the JSON sub-objects are coming back as strings instead of objects. The whole object is wrapped in unescaped double quotes _and_ all the quotes inside are escaped. It also doesn't explain why it works for the queries without the UNION. — Nicholas Tulach, Apr 03 '19 at 18:00

score 0 · Accepted Answer · answered Apr 04 '19 at 12:59

0

I solved this by eliminating the UNION altogether. In this case, it was as simple as adding an WHERE … OR language.short_name IS NULL to the first query.

answered Apr 04 '19 at 12:59

Nicholas Tulach

1,023
3
12
35

FOR JSON PATH returning escaped strings for nested objects

1 Answers1