Migrate Json Data From Azure Sql Db To Cosmos Db Results In String Values

I'm trying to migrate data from SQL DB using CosmosDB Data Migration Tool and I successfully migrated data from SQL DB but the result is all values are string Wondering if there's

Solution 1:

1.create a dataflow and use SQL DB as source.

2.In source option choose Query:

SQL:

selectjson_value(Data, '$.timestamp') astimestamp,
       json_query(Data, '$.Product.detail') as [Product.detail],
       json_value(Data, '$.Product.price') as [Product.price]

from test3

3.create a DerivedColumn,and change type of column.Expression of Product:

@(detail=split(replace(replace(replace(byName('Product.detail'),'[',''),']',''),'"',''),','),
        price=toDouble(byName('Product.price')))

4.choose Cosmos DB as sink and mapping like this:

5.create a pipeline and add the dataflow you created before,then click debug button or add trigger to execute it.

6.result:

{"Product":{"price":300.56,"detail":["eee","fff"]},"id":"d9c66062-63ce-4b64-8bbe-95dcbdcad16d","timestamp":1600329425}

---

Update:

You can enable the Data flow debug button, and see the result of expression in Data preview.

Solution 2:

One option is to export your SQL data to a plain CSV file, do any reformatting with your favorite tool, and import the cleaned CSV or JSON file using the Cosmos migration tool.

With PowerShell, for example, the process could be:

1. Export SQL data to CSV
2. Use PowerShell Import-CSV to read the data as an array of custom objects
3. Use PowerShell to modify the custom objects in memory to convert types, reformat, validate, etc
4. Export the cleaned data back to CSV or JSON using Export-CSV or ConvertTo-Json
5. Import the cleaned file using Cosmos Data Migration Tool