How to import documents that have arrays with the Cosmos DB Data Migration Tool

Question

I'm trying to import documents from a SQL Server database. Each document will have a list of products that a customer has bought, for example:

    {
        "name": "John Smith"
        "products": [
             {
                 "name": "Pencil Sharpener"
                 "description": "Something, this and that."
             },
             { 
                 "name": "Pencil case"
                 "description": "A case for pencils."
             }
        ]
    }

In the SQL Server database, the customer and products are stored in separate tables with a one-to-many relationship between the customer and products:

Customer

Id INT
Name VARCHAR

Product

Id INT
CustomerId INT (FK)
Name VARCHAR
Description VARCHAR

I've checked through the documentation , but can't see any mention of how to write the SQL query to map the one-to-many relationships to a single document.

I think there may be a way to do it as on the Target Information step (and when selecting DocumentDB - Bulk import (single partition collections)) there's the option to provide a bulk insert stored procedure. Maybe the products can be assigned to the document's products array from within there. I'm just not sure how to go about doing it as I'm new to Cosmos DB.

I hope that's clear enough and thanks in advance for your help!

Fei Han Fei Han · Accepted Answer · 2017-09-01T06:34:22

It seems that you’d like to return products info formatted as json when you import data from SQL Server using the Azure Cosmos DB: DocumentDB API Data Migration tool. Based on your customer and products table structure and your requirement, I do the following test, which works fine on my side. You can refer to it.

Import data from SQL Server to JSON file

Query

select distinct c.Name, (SELECT p.Name as [name], p.[Description] as [description] from [dbo].[Product] p where c.Id = p.CustomerId for JSON path) as products 
from [dbo].[Customer] c

JSON output

[
  {
    "Name": "Jack",
    "products": null
  },
  {
    "Name": "John Smith",
    "products": "[{\"name\":\"Pencil Sharpener\",\"description\":\"Something, this and that\"},{\"name\":\"Pencil case\",\"description\":\"A case for pencils.\"}]"
  }
]

Parsing the products

On the 'Target Information' step, you'll need to use your own version of BulkTransformationInsert.js. On line 32 is a 'transformDocument' function where you can edit the document. The following will parse the products and then assign them back to document before returning;

function transformDocument(doc) {
    if (doc["products"]) {
        let products = doc["products"];
        let productsArr = JSON.parse(products);
        doc["products"] = productsArr;
    }

    return doc;
}

How to import documents that have arrays with the Cosmos DB Data Migration Tool

1 Answers