How to verify the given node exists and read the value from azure data factory

Question

I have a lookup where I am performing a query to get the employee_no

SELECT employee_no FROM HRS.EMPLOYEE_MASTER WHERE SOC_SEC_NO = '@{variables('FE_SoSecNo')}' this might return the data as follows

{
    "firstRow": {
        "EMPLOYEE_NO": "12345"
    },
    "effectiveIntegrationRuntime": "VMSHIntegrationRuntime",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "SelfhostedIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ],
        "totalBillableDuration": [
            {
                "meterType": "SelfhostedIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ]
    },
    "durationInQueue": {
        "integrationRuntimeQueue": 3
    }
}

Or if there is no data found

{
    "effectiveIntegrationRuntime": "VMSHIntegrationRuntime",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "SelfhostedIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ],
        "totalBillableDuration": [
            {
                "meterType": "SelfhostedIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ]
    },
    "durationInQueue": {
        "integrationRuntimeQueue": 3
    }
}

I am adding a step to read the value as follows but when the json node does not exists, what I am trying to to insert if the node value was not present and update by getting the value

activity('GetEmpBySSN').output.firstRow.employee_no

Can I know how to write the logic to read the value

Answer 1

I created a Boolean variable to check if response of lookup contains firstrow node or not and the expression I am using to check the existence of the attribute is this

@bool(contains(activity('Lookup1').output, 'firstRow'))

My Boolean variable:

Set variable:

Output:

• Lookup output doesn't exist firstrow node:
• As firstrow node doesn't exist variable is returning false:

You can then use that boolean variable in an If activity, to get value of node if it exists conditionally based on the value of the variable.