如何使用Cosmos DB数据迁移工具导入具有数组的文档 [英] How to import documents that have arrays with the Cosmos DB Data Migration Tool

问题描述

我正在尝试从SQL Server数据库导入文档.每个文档将包含客户购买的产品列表，例如:

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."
             }
        ]
    }

在SQL Server数据库中，客户和产品存储在单独的表中，客户和产品之间存在一对多的关系:

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:

客户

Id INT
Name VARCHAR

产品

Id INT
CustomerId INT (FK)
Name VARCHAR
Description VARCHAR

我已经查看了文档 ，但没有提及如何编写SQL查询以将一对多关系映射到单个文档.

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.

我认为可能有一种方法可以在 Target Information 步骤中进行(并且在选择 DocumentDB-批量导入(单个分区集合)时)，可以选择提供批量插入存储过程.也许可以从那里将产品分配到文档的产品阵列.我只是不确定如何去做，因为我是Cosmos DB的新手.

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!

推荐答案

当您

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.

将数据从SQL Server导入JSON文件

查询

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输出

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

解析产品

在目标信息"步骤上，您需要使用自己的

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;
}

这篇关于如何使用Cosmos DB数据迁移工具导入具有数组的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！