Search code examples
oracleazure-data-factoryoracle-adf

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

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

enter image description here enter image description here

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

Solution

  • 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: enter image description here

    Set variable: enter image description here

    Output:

    • Lookup output doesn't exist firstrow node: enter image description here
    • As firstrow node doesn't exist variable is returning false: enter image description here

    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.