Azure Data Factory find JSON object by specific string/value then select a different value

Question

I have a Legacy API (with no Dev support) I am pulling data out of. Below is the output of activity().outputs.body.data

                {
                "column_id": 1,
                "type": "text",
                "varname": "foo_id",
                "label": "Foo ID",
                "value": "1234",
                "is_core": "yes",
                "orig_label": "Foo ID"
            },
            {
                "column_id": 23,
                "type": "text",
                "varname": "Foo_name",
                "label": "Foo name",
                "value": "blah blah",
                "is_core": "yes",
                "orig_label": "Foo name"
            },
            {
                "column_id": 15,
                "type": "text",
                "varname": "foo_bar",
                "label": "Foo Bar",
                "value": "beers beers beers",
                "is_core": "yes",
                "orig_label": "Foo Bar"
            }

Unfortunately, the Data is structured in such a way that if in the front end there is a blank value, then the API does not pass an Object. So, you may get 10 objects in the JSON, you may get 4. This means I cannot be lazy and select the value by the data[1].value.

I need to dynamically search for the varname, then in that object select the value.

So far, I have something like this:

@substring(coalesce(activity().outputs?.body?.data, ''),'foo_id').value

What I want to return in the above is '1234' if that object exists, or if it does not exist - just pass a ''

But I am not getting the output I expect. Once I have the dynamic content string sussed, I will be able to do the rest of them - but I am stuck on this one aspect.

Hopefully someone can help with the dynamic content query string.

Answer 1

• Create a new string variable to store the value of required output. Give the default value for that string as empty string.

• You can add the for each activity next to web activity that pulls the data from legacy API. In items of for-each setting, give the dynamic expression as @activity().outputs?.body?.data.

• Inside for-each activity, take an if activity and give the dynamic condition for if activity as @equals(item().varname,'foo2_id').

• Inside true section of if activity, add a set variable activity to set the value for the variable. Give the expression for that variable as @item().value.