Problem Importing JSON data in CosmosDB from Azure Data Factory Copy

Question

My Azure data Factory Pipeline has a COPY operation to COPY data from CSV to CosmosDB.
CSV file has 3 columns to import in CosmosDB.

CosmosDB Collection will receive:

Column1 in Partition Key
Column2 in Id
Column3 whole JSON Value

when I run COPY it copies the data in String format in CosmosDB for all 3 columns. Because there is no JSON or Map type available while we specify ColumnType.

What should I do to import JSON in value field, instead of String or TEXT. Below is the sample I am getting in CosmosDB:

any other replies..? or If we can PUSH JSON Data directly from Notebook to CosmosDB? — Anil Kapoor
@JayGong I am not allowed to use Azure Functions yet in our PROD. — Anil Kapoor

Jay Gong Jay Gong · Accepted Answer · 2020-04-02T07:41:16

Per my knowledge, no such features could help you convert string data to object format in adf cosmos db configuration.

So, as workaround, I suggest you using Azure Function Cosmos DB Trigger to process every document when they imported into database. Please refer to my function code:

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

namespace TestADF
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static void Run([CosmosDBTrigger(
            databaseName: "db",
            collectionName: "item",
            ConnectionStringSetting = "documentdbstring",
            LeaseCollectionName = "leases")]IReadOnlyList<Document> input, TraceWriter log)
        {
            if (input != null && input.Count > 0)
            {
                log.Verbose("Start.........");
                String endpointUrl = "https://***.documents.azure.com:443/";
                String authorizationKey = "key";
                String databaseId = "db";
                String collectionId = "item";

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

                for (int i = 0; i < input.Count; i++)
                {
                    Document doc = input[i];
                    if ((doc.GetPropertyValue<String>("Value") == null) || (!doc.GetPropertyValue<String>("Value")))
                    {                       
                        String V= doc.GetPropertyValue<String>("Value");
                        JObject obj = JObject.Parse(V);

                        doc.SetPropertyValue("Value", obj );

                        client.ReplaceDocumentAsync(UriFactory.CreateDocumentUri(databaseId, collectionId, doc.Id), doc);

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

                }
            }
        }
    }
}

Problem Importing JSON data in CosmosDB from Azure Data Factory Copy

1 Answers