How to split into Sub Documents with Nesting separator?

Question

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

Jay Gong Jay Gong · Accepted Answer · 2020-02-12T09:10:44

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.

How to split into Sub Documents with Nesting separator?

1 Answers