CODEWITHSUNDEEP
azure-data-factory
Satya Azure
Satya Azure

Reputation: 479

Lookup Activity and ForEach in Data Factory - Processing Nested JSON Values

I am trying to understand if it's possible to read the nested JSON values in a Data Factory. I have a look up activity followed by a for each to process the values from the lookup. I can read the values in the for each using the '@activity('lu_config_readentities').output.value' in case of simple json object.

With the below example JSON value, I can read the Query value using @item().Query but @item.Query.Term fails with an error.

Is this a known limitation? Is there anyway I could read the values?

JSON Sample: 

{
    "count": 1,
    "value": [
        {
            "Id": 63,
            "Source": "xxx",
            "EntityName": "test",
            "Query": {
                        "Term":"science",
                        "Database":"nih"
                     }
        }
    ],
    "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (Central US)",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.016666666666666666,
                "unit": "DIUHours"
            }
        ]
    },
    "durationInQueue": {
        "integrationRuntimeQueue": 11
    }
}

Error output: 

  "message": "The expression 'item().Query.Term' cannot be evaluated because property 'Term' cannot be selected. Property selection is not supported on values of type 'String'.",
    "failureType": "UserError",
    "target": "Set variable1"

Upvotes: 1

Views: 1080

Answers (1)

suziki
suziki

Reputation: 14113

You can follow below steps, I can success get the value in nested json.

This is my Lookup activity:

enter image description here

Then you can use name to get the value directly:

enter image description here

This is your json:

{
            "Id": 63,
            "Source": "xxx",
            "EntityName": "test",
            "Query": {
                        "Term":"science",
                        "Database":"nih"
                     }
}

This is the output of the Lookup activity on my side:

{
    "firstRow": {
        "Id": 63,
        "Source": "xxx",
        "EntityName": "test",
        "Query": {
            "Term": "science",
            "Database": "nih"
        }
    },
    "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (Central US)",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.016666666666666666,
                "unit": "DIUHours"
            }
        ]
    },
    "durationInQueue": {
        "integrationRuntimeQueue": 10
    }
}

And this is the expression should be(On my side, lookup activity is named Lookup1):

activity('Lookup1').output.firstRow.Query.Term

On my side I can get the nested value success, please have a try on your side.

Upvotes: 1

Related Questions