JSONVALUE ER function
The JSONVALUE function parses data in JavaScript Object Notation (JSON) format that is accessed at the specified path, and it extracts a scalar value that has the specified ID. It then returns the extracted scalar value as a String value.
Syntax
JSONVALUE (input, path)
Arguments
input: String
The valid path of a data source of the String type that contains JSON data.
path: String
The identifier of a scalar value of JSON data. Use a forward slash (/) to separate the names of related JSON nodes. Use the bracket ([]) notation to specify the index of a particular value in a JSON array. Note that zero-based numbering is used for this index.
Return values
String
The resulting text value.
Example 1
The JsonField data source contains the following data in JSON format: {"BuildNumber":"7.3.1234.1", "KeyThumbprint":"7366E"}. In this case, the expression JSONVALUE (JsonField, "BuildNumber") returns the following value of the String data type: "7.3.1234.1".
Example 2
The JsonField data source of the Calculated field type contains the following expression: "{""workers"": [ {""name"": ""Adam"", ""age"": 30, ""emails"": [""AdamS@Contoso.com"", ""AdamS@Hotmail.com"" ]}, { ""name"": ""John"", ""age"": 21, ""emails"": [""JohnS@Contoso.com"", ""JohnS@Aol.com""]}]}"
This expression configured to return a String value that represents the following data in JSON format.
{
"workers": [
{
"name": "Adam",
"age": 30,
"emails": [ "AdamS@Contoso.com", "AdamS@Hotmail.com" ]
},
{
"name": "John",
"age": 21,
"emails": [ "JohnS@Contoso.com", "JohnS@Aol.com" ]
}
]
}
In this case, the expression JSONVALUE(json, "workers/[1]/emails/[0]") returns the following value of the String data type: JohnS@Contoso.com.