Migrate JSON data from Azure SQL DB to Cosmos DB results in string values

Question

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 a way to convert those JSON to Object during migration process?

Here's my sample Query

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

from myTable

nesting seperator: .

@SteveZhao Sorry for late reply . the output is JSON document in Cosmos db container. Array and Object is double quotes string — FVBn
– FVBn, Commented Sep 21, 2020 at 0:54
Would you like to use Azure Data Factory? That can achieve this without exporting data from Cosmos db. — Steve Johnson
– Steve Johnson, Commented Sep 21, 2020 at 1:00
@SteveZhao I'll try. But my concern is my data stored in SQL DB is not normalized. It's a nvarchar type column that stored all JSON data. Is this still available for migration using Azure data factory? — FVBn
– FVBn, Commented Sep 21, 2020 at 1:24
I have tried this. Data in my SQL DB like this.And I can get this through Azure Data Factory. I would post it if you need. — Steve Johnson
– Steve Johnson, Commented Sep 21, 2020 at 1:38

Steve Johnson · Accepted Answer · 2020-09-21 07:29:56Z

1

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

2.In source option choose Query:

SQL:

select 
       json_value(Data, '$.timestamp') as timestamp,
       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.

edited Sep 21, 2020 at 7:29

answered Sep 21, 2020 at 2:55

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

FVBn Over a year ago

Step 3 is something like concat in js ?

Steve Johnson Over a year ago

Not actually. Step 3 changes String to Array. You can refer to this documentation.

FVBn Over a year ago

Sorry, But I don't see any new doc in Cosmos DB after published dataflow without any error. Did I miss something?

Steve Johnson Over a year ago

Maybe you didn't execute it. I update my answer and you can see the step 5.

FVBn Over a year ago

Thanks. It's working! By the way, is there playground website or some tools that can let us test the expressions (Step 3)?

|

Noah Stahl · Accepted Answer · 2020-09-17 14:34:38Z

1

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:

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

answered Sep 17, 2020 at 14:34

Noah Stahl

7,7235 gold badges30 silver badges42 bronze badges

2 Comments

FVBn Over a year ago

Does extra exporting gain fees ?

Noah Stahl Over a year ago

Importing to Cosmos will use your provisioned throughput. Not sure on SQL export.

Collectives™ on Stack Overflow

Migrate JSON data from Azure SQL DB to Cosmos DB results in string values

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related