Issue reading a variable JSON in Azure Data Factory

Question

My pipeline receives the Path and the name of a JSON file.

This is part of the flow that I created:

The lookup step is used to read the JSON File and later I need to get a very specific value using this formula in the set variable step:

@{activity('Lookup1').output.firstRow.Information.Area.AreaName}

This is an small example of part of the file:

{
  .....
  "Information": {
    "Area": {
      "AreaName": "General",
      "Workers":[
       ......
       ]
   }
}

But sometimes, I can get only this:

{
  .....
  "Information": null
}

With the first example I do not have any problem, but with the second I'm getting troubles because the value does not exist. My question is, how to avoid the error if the information that I need is not present? Something similar to the lax mode in sql server.

wBob · Accepted Answer · 2021-09-24 15:15:29Z

2

You can use in a single step using a similar logic, set as below value in dynamic value.

@if(equals(activity('Lookup1').output.firstRow.Information,null),'null',activity('Lookup1').output.firstRow.Information.Area.AreaName)

enter image description here

edited Sep 24, 2021 at 15:15

wBob

14.4k3 gold badges26 silver badges43 bronze badges

answered Sep 24, 2021 at 12:29

IpsitaDash-MT

1,4801 gold badge5 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wBob Over a year ago

I think you need the null to be without quotes @IpsitaDash-MT in order to match the OP's file? I could only get it to work this way. I've updated the post, hope that's ok. Upvote from me.

IpsitaDash-MT Over a year ago

Thanks very much @wBob for the update :)

Diego Eick Moreira · Accepted Answer · 2021-09-24 08:51:25Z

1

There is a hack for that...

Try to use an if when setting the variable.

Here is a pipeline created with three steps to show how this could work:

1. Lookup: read the json as you did.

2. FirstRow: set a variable with the content of the first row.

@{activity('Lookup1').output.firstRow}

3. Area: test if the variable contains "AreaName" and only if TRUE assign the AreaName to the variable.

if(contains(variables('FirstRow'), 'Area'),activity('Lookup1').output.firstRow.Information.Area.AreaName, null)

Reference: https://endjin.com/blog/2021/02/how-to-safely-reference-a-nullable-activity-output-in-synapse-pipelines-and-azure-data-factory

answered Sep 24, 2021 at 8:51

Diego Eick Moreira

945 bronze badges

Comments

virtualdvid · Accepted Answer · 2022-04-22 22:44:31Z

0

Here a cleaner approach:

@coalesce(activity('Lookup1').output?.firstRow?.Information, 'null')

Docs coalesce: Evaluates a list of expressions and returns the first non-null (or non-empty for string) expression.

And putting ? (safe-null operator) returns null if there is not firstRow or Information in the dot notation, check docs.

There are also more complex queries, docs:

The below example shows a complex example that references a deep sub-field of activity output. To reference a pipeline parameter that evaluates to a sub-field, use [] syntax instead of dot(.) operator (as in case of subfield1 and subfield2), as part of an activity output.
@activity('*activityName*').output.*subfield1*.*subfield2*[pipeline().parameters.*subfield3*].*subfield4* ```

edited Apr 22, 2022 at 22:44

answered Apr 22, 2022 at 17:24

virtualdvid

2,4413 gold badges18 silver badges35 bronze badges

Collectives™ on Stack Overflow

Issue reading a variable JSON in Azure Data Factory

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related