CODEWITHSUNDEEP
sqlsql-serverjson
Silly John
Silly John

Reputation: 1704

Translating JSON to a different JSON

I have a requirement to translate a JSON from One schema to another.

The JSON comes from a C# application, and I am not allowed to modify from there.

From the C# application, the JSON is stored to a nvarchar field in particular table.

The source coming from C# is formatted as follows:

{
    "officeSummary" : {
        "name" : "Test Office",
        "geographicData" : [{
                "addressLine1" : "AddressLine 1",
                "city" : "TestCity",
                "stateProvince" : "NJ",
                "postalCode" : "08543"
            }
        ],                  
    }
}

I need to translate it to this:

{
    "office" : {
        "name" : "Test Office",
        "officeAddress" : [{
                "streetAddress" : "AddressLine 1",
                "city" : "TestCity",
                "stateProvince" : "NJ",
                "postalCode" : "08543"
            }
        ],                  
    }
}

Is there any way we can translate this by change the key names using the SQL Server JSON functionalities? Like JSON_VALUE or JSON_MODIFY

Upvotes: 1

Views: 81

Answers (2)

Martin Thøgersen
Martin Thøgersen

Reputation: 1649

Based on this Example - Rename a key, you can use a 3-step process to rename a key. Below I have provided the code for renaming 'addressLine1' to 'streetAddress'.The same method can be used to replace the remaining values, although it becomes a bit messy. Notice, renamed key is moved to the end, but this should of course not affect json-parsing applications.

DECLARE @json AS NVARCHAR(MAX);
SET @json = '{
    "officeSummary" : {
        "name" : "Test Office",
        "geographicData" : [{
                "addressLine1" : "AddressLine 1",
                "city" : "TestCity",
                "stateProvince" : "NJ",
                "postalCode" : "08543"
            }
        ]                  
    }
}'

SELECT @json =
JSON_MODIFY(
    JSON_MODIFY(@json, '$.officeSummary.geographicData[0].streetAddress'
        , JSON_VALUE(@json, '$.officeSummary.geographicData[0].addressLine1')
    )
, '$.officeSummary.geographicData[0].addressLine1', NULL)

PRINT @json

Result

{
    "officeSummary" : {
        "name" : "Test Office",
        "geographicData" : [{

                "city" : "TestCity",
                "stateProvince" : "NJ",
                "postalCode" : "08543"
            ,"streetAddress":"AddressLine 1"}
        ]                  
    }
}

Upvotes: 1

Martin Thøgersen
Martin Thøgersen

Reputation: 1649

I think you could use modify json .net libraries functionality from Newtonsoft combined with a SSIS script transformation task (assuming you have SSIS available).

Then you could read the property value into a variable. Delete the property and create a renamed property with the saved value.

Upvotes: 0

Related Questions