Forum Discussion

cplewis's avatar
cplewis
Copper Contributor
Feb 23, 2023

deserializing string as json during copy step

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?

  • manishnarang's avatar
    manishnarang
    Copper Contributor

    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

Resources