How to Handle Case Sensitivity in Dynamic JSON Input Keys ("A" vs "a") for Derived Columns in ADF Dataflow Pipeline?

Hrithik Purwar 25

I am working on a dataflow pipeline in ADF with an input dataset schema that includes a column 'A'. The dataflow processes various JSON files with dynamic schemas, where some JSON files use 'A' and others use 'a' as keys. Since the schema is case insensitive, I cannot include both 'A' and 'a' in the input dataset schema. Currently, I have a derived column dependent on the value of 'A'/'a' (it combines <A> + '/' + <date> and the sink stores the folders based on the derived column path data), and it functions correctly for JSON files containing 'A'. How can I modify the dataflow to handle JSON files with 'a' as the key? Is there any other workaround to achieve this in ADF?

Here are examples of two JSON files with dynamic schemas that I'm working with: Example 1 Example 2

NIKHILANETHIKUNTA 1,110 Reputation points Microsoft Vendor

2024-07-18T09:11:58.0433333+00:00
Hi @Hrithik Purwar
Thank you for your question and using the Microsoft Q&A platform.

In Azure Data Factory (ADF), you can handle this situation by using a conditional expression in your derived column transformation. The idea is to check if the ‘A’ column exists, and if not, use the ‘a’ column. Here’s how you can do it:

In your dataflow, add a Derived Column transformation after your Source transformation.

In the Derived Column settings, add a new column. You can name it something as ‘UnifiedColumn’.

In the expression for ‘UnifiedColumn’, use the following conditional expression:

iif(isNull('A'), 'a', 'A')

This expression checks if ‘A’ is null. If it is, it uses the value of ‘a’. If ‘A’ is not null, it uses the value of ‘A’.

Now, you can use ‘UnifiedColumn’ in your pipeline instead of ‘A’ or ‘a’. This column will always have the value of ‘A’ or ‘a’, depending on which one exists in the current JSON file.

Hope this helps. Please let me know if you have any further questions.
Hrithik Purwar 25 Reputation points

2024-07-18T11:14:29.32+00:00

Hey @NIKHILANETHIKUNTA , I tried the solution but instead of taking up the "a"'s value it is taking "A" as the string itself
My expected derived column value is {derived:"hhhh"}, {derived:"jjjj"}
But the actual derived column value is coming like {derived:"A"}, {derived:"A"}
NIKHILANETHIKUNTA 1,110 Reputation points Microsoft Vendor

2024-07-19T11:57:47.4733333+00:00

Hi @Hrithik Purwar
Could you please share the screenshot of the dataflow configuration and also provide some more details on your present approach so that we can investigate further?
Thank you.
NIKHILANETHIKUNTA 1,110 Reputation points Microsoft Vendor

2024-07-22T06:47:47.19+00:00

Hi @Hrithik Purwar
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
NIKHILANETHIKUNTA 1,110 Reputation points Microsoft Vendor

2024-07-23T18:44:07.88+00:00

Hi @Hrithik Purwar
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

How to Handle Case Sensitivity in Dynamic JSON Input Keys ("A" vs "a") for Derived Columns in ADF Dataflow Pipeline?

Your answer