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. 

cplewis_0-1677192700957.png

 

This is what how it shows up in CosmoDB:

cplewis_1-1677192769692.png

 

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

@cplewis 

Hello,
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.

 

Best,

Manish