Search code examples
azure-data-factory

How to extract the value from a json object in Azure Data Factory

I have my ADF pipeline, Where my final output from set variable activity is something like this {name:test, value:1234},

The input coming to this variable is

{
    "variableName": "test",
    "value": "test:1234"
}

The expression provided in Set variable Item column is @item().ColumnName. And the ColumnName in my JSon file is something like this "ColumnName":"test:1234"

How can I change it so that I get only 1234. I am only interested in the value coming here.

Solution

  • It looks like you need to split the value by colon which you can do using Azure Data Factory (ADF) expressions and functions: the split function, which splits a string into an array and the last function to get the last item from the array. This works quite neatly in this case:

    @last(split(variables('varWorking'), ':'))

    Sample results:

    enter image description here

    Change the variable name to suit your case. You can also use string methods like lastIndexOf to locate the colon, and grab the rest of the string from there. A sample expression would be something like this:

    @substring(variables('varWorking'),add(indexof(variables('varWorking'), ':'),1),4)

    It's a bit more complicated but may work for you, depending on the requirement.