How to transform SQL result with complex JSON column containing dots in properties using Azure Data-CodePudding

Using an Azure Data Factory Flow I'm reading data from an Azure SQL database. The result set has some simple columns, such as Uid nvarchar(100) but one column is JSON content Json nvarchar(max).

The JSON content structure looks like this (there is one JSON document per row):

{
  key: "Foo",
  dt.topic : "Foo Bar"
}

I'm now trying to transform the JSON string of the result set into a parsed JSON object using a "Parse Transform" containing both, key and dt.topic. I can successfully map key using the expression (key as string).

But how can I map dt.topic? This doesn't work:

(dt.topic as string)

The moment I add a property with with a dot in it, there are red squigglies all over the place in the editor and if I decide to "Save and finish" I get the error "Expression type could not be evaluated, correct the expression."

Interestingly, using "Detect type" as suggested in the comments would add the properties just like I did, using the dot notation, resulting in the very same error.

CodePudding user response：

The column name with special characters should be included within braces {} in the Azure data factory.

Example:

To parse the JSON value with dot in the property name, the property name should be used within braces {}.

({dt.topic} as string)