如何使用嵌套分隔符拆分为子文档? [英] How to split into Sub Documents with Nesting separator?

问题描述

我正在通过Azure cosmos DB数据迁移工具将数据从SQL迁移到COSMOS DB

有人可以帮忙迁移子文档中的数据，如何在嵌套分隔符中指定

  SELECT TOP 5 P.ProjectDocumentId，P.ProjectId，PU.UpdatedByFullName从[文档].[ProjectDocuments] P内部联接[文档].[ProjectDocumentUpdate] PU ON P.ProjectDocumentID = PU.ProjectDocumentIDW.P.ProjectDocumentId ='7DA0011B-7105-4B6C-AF13-12B5AC50B608' 

结果:

Cosmos DB中的预期文档:

  {"ProjectDocumentId":"7da0011b-7105-4b6c-af13-12b5ac50b608"，"ProjectId":"ed1e0e47-ff1c-47be-b5e9-c235aef76161"，"ProjectDocumentUpdate":{"UpdatedByFullName":"Unnati"}，{"UpdatedByFullName":"Eugene"}，{"UpdatedByFullName":"Meghana"}} 

解决方案

根据您的描述，您需要的不是简单地将嵌套的JSON数据生成到cosmos db中.您想生成的JSON包含按列划分的Json数组组.通过相同的 ProjectDocumentId 和 ProjectId 来 UpdatedByFullName .

基于我的测试和

SQL:

 选择s1.name，'ageArr'=(选择AS年龄从dbo.student为s2其中s2.name = s1.nameJSON路径)来自dbo.student作为s1按s1.name分组FOR JSON路径； 

输出如下:

然后您将获得这样的json输出，以便可以将其简单地导入到cosmos db中.

I am migrating data from SQL to COSMOS DB through Azure cosmos DB Data Migration Tool

Can someone please help help to migrate data in Sub-Documents, how to specify in nesting separator

SELECT TOP  5 P.ProjectDocumentId, P.ProjectId, PU.UpdatedByFullName
FROM [Docs].[ProjectDocuments] P
INNER JOIN [Docs].[ProjectDocumentUpdate] PU ON P.ProjectDocumentID = PU.ProjectDocumentID
WHERE P.ProjectDocumentId = '7DA0011B-7105-4B6C-AF13-12B5AC50B608'

Result:

Expected Document in Cosmos DB:

{
  "ProjectDocumentId": "7da0011b-7105-4b6c-af13-12b5ac50b608",
  "ProjectId": "ed1e0e47-ff1c-47be-b5e9-c235aef76161",
  "ProjectDocumentUpdate": { 
    "UpdatedByFullName" : "Unnati" 
  }, { 
    "UpdatedByFullName" : "Eugene" 
  },
  { 
    "UpdatedByFullName" : "Meghana" 
  }             
} 

解决方案

According to your description,your need is not simple producing nested JSON data into cosmos db.You wanna producing JSON contains Json array group by some columns.Something like merge UpdatedByFullName by same ProjectDocumentId and ProjectId.

Based on my test and some researches on the Migration Tool document,it seems that Import data from SQL SERVER feature can't handle with producing json array group by some columns.

So,i figure out a workaround which is lead by this case:SQL to JSON - Grouping Results into JSON Array and this doc.My sample data as below:

SQL:

select s1.name,
'ageArr' = (
            SELECT
                age AS 'age' 
            FROM
                dbo.student as s2
            where s2.name = s1.name
            FOR JSON PATH)
from dbo.student as s1
group by s1.name
FOR JSON Path;

Output as below:

Then you have such json output so that you can import it into cosmos db simply.

这篇关于如何使用嵌套分隔符拆分为子文档?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！