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.

CodePudding user response：

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

CodePudding user response：

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)