deserializing string as json during copy step

Copper Contributor

I have a copy activity where the source is Salesforce and the sink is CosmosDB. 

Here is the source - hedge_data__c is a string field, but it's actually json. 



This is what how it shows up in CosmoDB:



I ultimately want the hedge_data__c json to show up in CosmosDB as json. 

I have tried updating the mapping to use the conversion function "json()" to deserialize the string,

but that doesn't seem to work. Any suggestions on how I can let ADF know that this string field from Salesforce

should be treated as JSON in CosmosDB? 


Bonus question: I ultimately want to transform the hedge_data__c json  - I have it working on a single

entry using jq:

cat hedge.json|jq '[.[][] | select(.isOpen == true)] |map({startDate, percentageVolume: (100-.percentageVolume)}) |sort_by(.startDate)'
    "startDate": "2022-12-01",
    "percentageVolume": 0
    "startDate": "2023-01-01",
    "percentageVolume": 25
  }, etc.


How can I do this sort of transformation in ADF?

1 Reply


Since this query is running against Salesforce, no idea of the syntax to treat this column as a json.
But you can use Data Flows instead, choose your table as a source and use "Parse" transformation for hedge_data__c column.


Or if you have already resolved it, please inform how you did it.