CODEWITHSUNDEEP
azure-data-factory
ZZZSharePoint
ZZZSharePoint

Reputation: 1341

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.

Upvotes: 4

Views: 20555

Answers (3)

Marcus Kwok
Marcus Kwok

Reputation: 1

Just find out below function work for me in ADF.

@json(string(item())).value

Upvotes: 0

wBob
wBob

Reputation: 14379

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.

Upvotes: 1

KarthikBhyresh-MT
KarthikBhyresh-MT

Reputation: 5034

It seems like you are using it inside of an iterator since you got item but however, I tried with a simple json lookup value

@last(split(activity('Lookup').output.value[0].ColumnName,':'))

enter image description here

Upvotes: 2

Related Questions