Inverse of STRING_ESCAPE() in T-SQL for escaped JSON-CodePudding

I have some unfortunately malformed JSON, where the inner JSON object (the output value) is ESCAPED, and prevents being read properly.

Example:

DECLARE @jsonContent nvarchar(max) = '{"output":"{\n\"latest_activity\": \"2021-12-04T08:32:19.343228\"\n}"}'

SQL server does not seem to like this - the below returns NULL

  SELECT JSON_VALUE(@jsonContent, '$.output.latest_activity') AS latest_activity

However, the following gets the desired result, by replacing the escaped double quotes with just double quotes:

  SELECT CAST(
            JSON_VALUE(
                REPLACE(
                    JSON_VALUE(@jsonContent, '$.output')
                    , '\"', '')
                , '$.latest_activity')
            AS datetime2(7))
        AS [latest_activity]

Is there a better way to get this done, and handle other escaped characters?

I was hoping for the opposite of the built-in STRING_ESCAPE function, but alas does not exist.

CodePudding user response：

This JSON isn't malformed as such, it is just stringified JSON. You need to get the output property with JSON_VALUE, then use JSON_VALUE again.

DECLARE @jsonContent nvarchar(max) = '{"output":"{\n\"latest_activity\": \"2021-12-04T08:32:19.343228\"\n}"}';

SELECT JSON_VALUE(JSON_VALUE(@jsonContent, '$.output'), '$.latest_activity');

db<>fiddle