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.

Answer 1

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)

Answer 2

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

Answer 3

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* ```