How to add escape character in derived column and while reading json with just one escape character?

Question

How to add escape character in derived column and while reading json with just one escape character?

Rajendran, Prakash 1

This is my expression in derived column:
concat("\/Date(",toString(toLong(toLong(toTimestamp(concat(toString({SOWING FEMALE}, 'yyyy-MM-dd'), " 00:00:00") ,'yyyy-MM-dd HH:mm:ss')-toTimestamp('1970-01-01 00:00:00.000','yyyy-MM-dd HH:mm:ss.SSS'))/1000l)), '000+0200)\/')

My expected output is "/Date(1631577600000+0200)/"

but when I store data in as json format, it gets stored into "\/Date(1631577600000+0200)\/". How can I keep just one escape character?

7 answers

Your answer

Answer 1

Michael Taylor 60,161

The data is correct. JSON uses '\' as an escape character. Therefore if you want a single '\' in the final data you have to put '\' in the JSON. Otherwise when the data is read out of the JSON it comes back wrong. This is identical to how C++ and C# work with their string literals.

So, "\/Date" will be read out of the JSON as \/Date. But "/Date" would either produce an error or potentially just be Date, depending upon the parser.

Rajendran, Prakash 1 Reputation point

2021-12-15T15:24:44.307+00:00

@Michael Taylor yeah understood your point. I am out of ideas how to put '\' in the JSON. I read the stored JSON in the look up activity and not sure how to mention one more escape character into that.
Michael Taylor 60,161 Reputation points

2021-12-15T15:38:33.34+00:00

To be clear it isn't your responsibility to put the escape character in the string in most cases. Presumably you're using some function or writer to generate the JSON. It'll handle the escaping for you. So if you pass \blah to the JSON writer it should convert it automatically to \\blah and it'll do the inverse on a read. The only time you would need to do this manually is if you were trying to write JSON by hand which isn't generally recommended.

As an example, if you're using the json expression json('\/Date') then it should return back the JSON formatted string \\Date/.
Rajendran, Prakash 1 Reputation point

2021-12-16T13:42:45.563+00:00

@Michael Taylor Just curious, do you think if I read the JSON file which has this input "/Date(1631577600000+0200)/" through custom activity (python) might help to pass the input value to API as it is "/Date(1631577600000+0200)/" instead of converting to UTC format ? I am just checking if this will work before requesting to the client for batch pool.
Michael Taylor 60,161 Reputation points

2021-12-16T15:20:00.527+00:00

The conversion is happening as part of the JSON serializer so it depends on the serializer. Do you need the offset in the date? If not then set it to +0000 to treat it as UTC (hence no conversion). If you do need it could you show the logic (or UX) that you're using to convert that string you showed initially to JSON?

I'm also wondering about the title of the post. It says reading JSON but I believe your ADF is writing the JSON to a third party API correct?

Answer 2

Rajendran, Prakash 1

yes actually I do not want to add escape character but what happened was my initial input looked like this "/Date(1631577600000+0200)/". This then I pass to API. JSON reader automatically converts this into UTC format before calling API. That's when I thought to use escape character but no luck.

Michael Taylor 60,161 Reputation points

2021-12-15T16:52:18.737+00:00

I see. Unfortunately escape characters aren't going to work here. The JSON reader is seeing Date(...) and converting it to UTC and escape characters aren't going to change that.

Firstly I'd ask the question why it matters? APIs that support date(time) values should handle the value in any format. They can tell the difference between UTC and timezone-offset values. It is up to them to determine how to handle it. In JSON the value is actually just a string. JSON doesn't directly support "date", it is just one of the many possible string values that can be sent.

To work around this it is going to depend upon what your API is actually expecting. If it is an older API then it might not understand timezones or UTC at all. In that case consider getting rid of the Date() and just pass the raw date value such as (yyyy-MM-dd). If it requires that you use Date() then drop the offset. The reader won't be able to tell whether it is local or UTC and should just treat it as UTC. Alternatively you might need to convert to UTC on your side. Again, it depends on what the API is expecting. In the long term though the API should be updated to support dates in any format (UTC or timezone offset) as this is the standard.
Michael Taylor 60,161 Reputation points

2021-12-15T16:53:07.1+00:00

BTW here's an interesting article where someone seems to have been running into a similar issue with ADF and how they solved it.

Answer 3

Rajendran, Prakash 1

I agree with all your points. I have already put those questions to my team. Just like mentioned in the article ,same here that API is out of control as it is maintained by the client. So I have to check with them. I will also see if the article can give me an work around. Thank you very much @Michael Taylor . I will post the update.

Answer 4

Rajendran, Prakash 1

Yes.. Like I mentioned I thought adding an escape character would solve this issue but unfortunately not and hence the title of this post as such. But it's true that JSON is writing towards 3rd party API. My oringal logic as below

concat("/Date(",toString(toLong(toLong(toTimestamp(concat(toString({date_column}, 'yyyy-MM-dd'), " 00:00:00") ,'yyyy-MM-dd HH:mm:ss')-toTimestamp('1970-01-01 00:00:00.000','yyyy-MM-dd HH:mm:ss.SSS'))/1000l)), '000+0200)/')

Michael Taylor 60,161 Reputation points

2021-12-16T16:33:25.447+00:00

Right, I understand the expression you're using to generate the string value but I'm trying to understand how you're then converting that to JSON as part of passing it to your API. Is this string being set as the requestBody in your ADF's Copy Activity task? Are you using the REST as Source or REST as Sink approach here? I'm assuming this is a POST/PUT request and not a GET as GET requests cannot have a body. Are you using a mapping step to map the data to the JSON body before you make the request. If so then for the specific date field did you set it to DateTime or String for the type? If you're currently using DateTime then consider changing it to String and see if the JSON serializer skips the conversion (it should).

Answer 5

Rajendran, Prakash 1

The below screenshots tell you the flow. I use look up to call the json files and send each row into for each loop which has web activity.

Michael Taylor 60,161 Reputation points

2021-12-16T17:46:19.047+00:00

Please use the comment button to respond to answers so we can more easily track the discussion.

So you're not even using the REST task so there should be no JSON conversion happening I believe. But given your screenshot it looks you already have created the JSON in an earlier step and your web request is literally just taking the string value that is stored in @item() and passing it along. Can you confirm that @item() has the messed upon JSON already? If so then the issue is in your earlier step where Lookup is getting the JSON so we need to take a step back and look at how that data is getting populated.
Rajendran, Prakash 1 Reputation point

2021-12-16T19:15:06.307+00:00

yeah I have already created the JSON in the dataflow before I call the look up. I am posting the data flow screenshot which stores the JSON file in here. If you look at the 3rd screen shot , that's the output from look up activity during debug. Date is not read as string. When I open my JSON file, I can see this is how data stored as "Completed_date":"/Date(1620000000000+0200)/" But the output from look up says otherwise.
Michael Taylor 60,161 Reputation points

2021-12-16T20:38:06.503+00:00

Can you post what the ConvertDateTo... task is doing? Is that where your expression that you initially posted is coming from? I believe there is an option in the transformation task to preview the data. Is the data correct in the preview.

Also, to clarify I assume that you confirmed that the actual JSON sent to the API is what is also shown in the Output window you posted. Just want to make sure the issue here isn't just the output view converting data but the actual API gets the correctly formatted data.
Rajendran, Prakash 1 Reputation point

2021-12-17T07:42:54.22+00:00

Yes, data preview on the date conversion logic is working as expected. Yes the output preview is the JSON sent to API and it converts the original unix date format.
Rajendran, Prakash 1 Reputation point

2021-12-17T09:06:46.503+00:00

The below screenshot is the preview of JSON connection. The JSON reader converts the date to UTC format. Unfortunately it does not consider that as just a string.
Michael Taylor 60,161 Reputation points

2021-12-17T16:02:04.667+00:00
The date is fine in your JSON file, the date is converted to UTC when loaded. That seems to be an issue when reading the JSON file. I don't know that you can change that behavior but you could adjust your derived column expression to produce the output you want I think. Something like this maybe: convertFromUtc(<date>, '<desired timezone>'), 'zzz')

The problem is that Date(...) is a .NET serializer format and not a standard format. I'm assuming you're trying to solve this issue because the UTC value being passed to the API is failing the call but the .NET serializer should be able to handle that format so maybe this isn't an issue after all.

But if the API really does require this very specific format then you're in for some extra code. You cannot get the total seconds since 1970 from the date directly. You have to calculate that value and it must be done against the date in the time zone format you want. Then you can build a string to produce the final output. Just taking a guess here on what kind of expression you'd need.

localTime = convertFromUtc(<date>, '<desired timezone>') concat('\\Date(', div(sub(ticks(localTime), ticks('1970-01-01Z')), 10000), formatDateTime(localTime, 'zzz'), ')/')

Ideally you can calculate the localTime for a row into a derived column and then create another derived column that relies on it otherwise you'll have to put that entire expression in each place it is needed. Here's the general idea though.
Rajendran, Prakash 1 Reputation point

2021-12-20T07:50:12.863+00:00

Yeah, in here API does accept only the specific format. That's an unfortunate. I have even tried with Azure function writing a small piece of python code but anything that comes out of any activity as JSON format, it seems JSON reader converts the date into UTC format. So it's a dead lock situation. I am conveying this to client.

Share via

How to add escape character in derived column and while reading json with just one escape character?

7 answers

Your answer