将JSON数据从Azure SQL DB迁移到Cosmos DB会产生字符串值 [英] Migrate JSON data from Azure SQL DB to Cosmos DB results in string values

问题描述

我正在尝试使用CosmosDB数据迁移工具从SQL DB迁移数据，并且成功从SQL DB迁移了数据，但结果是所有值都是字符串

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

想知道是否可以在迁移过程中将这些JSON转换为Object吗?

Wondering if there's a way to convert those JSON to Object during migration process?

这是我的示例查询

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

嵌套分隔符:.

推荐答案

1.创建一个数据流，并使用SQL DB作为源.

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

2.在源选项中，选择查询:

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.创建一个 DerivedColumn ，并更改列的类型. Product 的表达方式:

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.选择Cosmos DB作为接收器和映射，如下所示:

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

5.创建管道并添加之前创建的数据流，然后单击调试按钮或添加触发器以执行它.

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

6.结果:

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

---

更新:

您可以启用数据流调试"按钮，并在数据预览"中查看表达式的结果.

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

这篇关于将JSON数据从Azure SQL DB迁移到Cosmos DB会产生字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！