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

Pratik Lad · Accepted Answer · 2023-12-21 07:06:08Z

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

Set variable: enter image description here

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.

answered Dec 21, 2023 at 7:06

Pratik Lad

8,7262 gold badges5 silver badges15 bronze badges

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

13 Comments

Developer Over a year ago

@pratik Lad thanks can we get the actual value when the json is present?

Pratik Lad Over a year ago

yes, after this use if condition based on the result of node existence if true take set variable inter true condition take another set variable to access node value.

Developer Over a year ago

You mean to say like @equals(variables('emp_exists').value,'true')

Pratik Lad Over a year ago

yes, and under if condition fetch the value of node if exist

Developer Over a year ago

Got it my bad I need to call it by @{variables('employee_no')}

|

Collectives™ on Stack Overflow

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

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related