CODEWITHSUNDEEP
azure-data-factoryazure-databricks
Developer Rajinikanth
Developer Rajinikanth

Reputation: 354

How to use ForEach Loop for DataBricks Notebook Json object output

I am trying to fetch the notebook value(json value) to loop ForEach activity. how do we use this approach. also my goal is ForEach activity needs to all lookup activity to fetch some details from azure SQL DB to concatenate the values to send another notebook.

Kindly advice and possibility way to integrate.

sample JSON out from notebook

{
    "runPageUrl": "https://adb-url",
    "runOutput": {
        "value": {
            "xxxx": 60,
            "mmmm": "value",
            "db": "dbname",
            "table": "tablename",
            "asset_id": "1cda102e-dddddxxxxx9c775",
            "aggvalue": [
                "value"
            ],
            "date_val": [
                "date_val"
            ]
        },
        "vlue2": "value": {
            "xxxx": 60,
            "mmmm": "value",
            "db": "dbname",
            "table": "tablename",
            "asset_id": "1cda102e-dddddxxxxx9c775",
            "aggvalue": [
                "value"
            ],
            "date_val": [
                "date_val"
            ]
        },
        
    "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime",
    "executionDuration": 297,
    "durationInQueue": {
        "integrationRuntimeQueue": 0
    },
    "billingReference": {
        "activityType": "ExternalActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.3343434,
                "unit": "Hours"
            }
        ]
    } }

Upvotes: 0

Views: 1204

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11454

I tried your JSON in my environment and found that your JSON is not valid with below error in databricks.

enter image description here

Then I used below JSON.

{
    "value": {
        "xxxx": 60,
        "mmmm": "value",
        "db": "dbname",
        "table": "tablename",
        "asset_id": "1cda102e-dddddxxxxx9c775",
        "aggvalue": [
            "value"
        ],
        "date_val": [
            "date_val"
        ]
    },
    "value": {
        "xxxx": 60,
        "mmmm": "value",
        "db": "dbname",
        "table": "tablename",
        "asset_id": "1cda102e-dddddxxxxx9c775",
        "aggvalue": [
            "value"
        ],
        "date_val": [
            "date_val"
        ]
    }}

When I execute the above JSON, you can see the same key value is updated.

enter image description here

When I pass the same JSON to ADF pipeline using dbutils.notebook.exit(data) and I have used the @activity('Notebook1').output.runOutput dynamic content in the ForEach. You can see I got same error as yours.

enter image description here

As the error suggests, ForEach only supports array or string to iterate.

So, change your JSON like below (array of objects) and pass it to the ForEach which is good for iterating.

[
{
    "xxxx": 60,
    "mmmm": "value",
    "db": "dbname",
    "table": "tablename",
    "asset_id": "1cda102e-dddddxxxxx9c775",
    "aggvalue": ["value"],
    "date_val": ["date_val"]
},
{
    "xxxx": 60,
    "mmmm": "value",
    "db": "dbname",
    "table": "tablename",
    "asset_id": "1cda102e-dddddxxxxx9c775",
    "aggvalue": ["value"],
    "date_val": ["date_val"]
}
]

enter image description here

Coming to using lookup values, ForEach only supports iterating one array(Notebook output). So, if you want to concatenate the lookup values also inside this ForEach, both Notebook output array length and lookup output array length should be same.

Then you can iterate two arrays at a time by using an index array with ForEach activity. To understand about iterating about 2 arrays at same time go through this SO answer.

Upvotes: 0

Related Questions