How to avoid escape character in Migrating the data from SQLDatabase to Cosmos DB

Question

I want to migrate the whole SQL database to Cosmos DB.in this process one of the SQL table columns have a serialize data as follows

    [{"Id":"1","Type":"Phone","HeaderLabel":"HQ - Main Line","ContactNumber":"+9122222222"}]

the serialized data represent a Class

  public class ContactNumber
{
    public string ContactNumberId { get; set; }
    public string Type { get; set; }
    public string HeaderLabel { get; set; }
    public string ContactNumber { get; set; }
}

while saving the data in sql i have to perform the Serialization and Deserialization for the class which ncessary to do.

 public string _ContactNumbers { get; set; }

 public List<ContactNumber> ContactNumbers
    {
        get { return _ContactNumbers == null ? null : JsonConvert.DeserializeObject<List<ContactNumber>>(_ContactNumbers); }
        set { _ContactNumbers = value == null ? null : JsonConvert.SerializeObject(value); }
    }

after using the migration tool it gets updated like this

"ContactNumbers":"[{\"Id\":\"1\",\"Type\":\"Phone\",\"HeaderLabel\":\"HQ - Main Line\",\"ContactNumber\":\"+9122222222\"}]"

the class remains the same. while fetching the data from cosmos DB I have not performed any Serialization and Deserialization.

public List<ContactNumber> ContactNumbers

while fetching the data it throws an error

Error converting value "[{"Id":"1","Type":"Phone","HeaderLabel":"HQ - Main Line","ContactNumber":"+9122222222"}]" to type 'System.Collections.Generic.List`1[CosmosDB.Models.ContactNumber]'. Path 'ContactNumber', line 1, position 2411.

the Error comes because of the Extra \ character in the string which is added after the migration.

I don't want to Serialization and Deserialization the class in cosmos DB because it not necessary to do it.

so how can I avoid the extra \ while migrating the data from SQL Database to Cosmos DB Document?

Please provide some additional details, such as a pointer to the specific tool that you're using, how you are calling it, etc. — Brendan Green, Jul 12 '18 at 13:52
Azure Cosmos DB Data migration tool is used https://learn.microsoft.com/en-us/azure/cosmos-db/import-data @BrendanGreen — Satyaray Singh, Jul 12 '18 at 13:55
So which API are you using, are you using the GUI or command line version? — Brendan Green, Jul 12 '18 at 13:55
Ok, you're really not giving much to work with here. I've reread your question, and you have a class that's been serialized into a string. Those leading slashes are there to escape following quote character. That is, the resultant JSON has a property `ContactNumbers`, and contains a string value, which has embedded quotes that need to be escaped. That all said, you haven't indicated WHY this is an issue. — Brendan Green, Jul 12 '18 at 22:16
@JayGong I'm new to Azure Function Cosmos DB Trigger so, I have done each and every step given in this video = > https://azure.microsoft.com/en-in/resources/videos/create-an-azure-cosmos-db-trigger-in-an-azure-function-in-visual-studio/ but while doing that it throws an error " The listener for function 'Function' was unable to start. Microsoft.Azure.Documents.ChangeFeedProcessor: Object reference not set to an instance of an object." — Satyaray Singh, Jul 16 '18 at 08:38
@SatyaraySingh You could try to find solutions from the link:https://stackoverflow.com/search?q=%5Bazure-functions%5D+Object+reference+not+set+to+an+instance+of+an+object — Jay Gong, Jul 16 '18 at 09:37

Jay Gong · Accepted Answer · 2018-07-20T01:51:43.620

You could use Azure Function Cosmos DB Trigger to process every document which is created. Please refer to my function code:

using System;
using System.Collections.Generic;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Newtonsoft.Json.Linq;

namespace ProcessJson
{
    public class Class1
    {
        [FunctionName("DocumentUpdates")]
        public static void Run(
        [CosmosDBTrigger(databaseName:"db",collectionName: "item", ConnectionStringSetting = "CosmosDBConnection",LeaseCollectionName = "leases",
            CreateLeaseCollectionIfNotExists = true)]
        IReadOnlyList<Document> documents,
        TraceWriter log)
        {
            log.Verbose("Start.........");
            String endpointUrl = "https://***.documents.azure.com:443/";
            String authorizationKey = "***";
            String databaseId = "db";
            String collectionId = "import";

            DocumentClient client = new DocumentClient(new Uri(endpointUrl), authorizationKey);

            for (int i = 0; i < documents.Count; i++)
            {
                Document doc = documents[i];
                if((doc.alreadyFormat == Undefined.Value) ||(!doc.alreadyFormat)){
                   String info = doc.GetPropertyValue<String>("info");
                   JArray o = JArray.Parse(info);

                   doc.SetPropertyValue("info", o);
                   doc.SetPropertyValue("alreadyFormat", true);
                   client.ReplaceDocumentAsync(UriFactory.CreateDocumentUri(databaseId, collectionId, doc.Id), doc); 

                   log.Verbose("Update document Id " + doc.Id);

                }

            }
        }
    }
}

In addition, please refer to the case: Azure Cosmos DB SQL - how to unescape inner json property

Hope it helps you.

It's Works. just one problem that the trigger gets fired 2 times. first time when we are inserting the Document and second time after we update the Document in Azure Function Trigger. is that possible to fire the trigger just once while we are inserting the Document not after updating? — Satyaray Singh, Jul 16 '18 at 13:18
@SatyaraySingh Sorry for delay, you could try to add a property such as 'alreadyFormat' and check it's value to control the update operations. — Jay Gong, Jul 20 '18 at 01:46

How to avoid escape character in Migrating the data from SQLDatabase to Cosmos DB

1 Answers1

Linked