question

AbdullaMahammadKhanDawood-5577 avatar image
0 Votes"
AbdullaMahammadKhanDawood-5577 asked AbdullaMahammadKhanDawood-5577 commented

How to read specific column value through lookup activity when the source file is JSON format in ADF

Hi All,

Good Day!!

We have a requirement to read JSON format file through lookup activity and only select the specific field from output.

Here I am enclosing the snip of the json where we need to fetch only one specific column id in next subsequent foreach control flow in pipeline where we use it as input paramter in copy activity rest api input in order to achieve selecting id value thorugh dynamic expression.

The below expression is throwing error

@activity('LookupConfigFiles').output.value.results.id

Appreciate if any help on this.

Thank you in anticipation!!

Regards,
Mahammad Khan

120817-image.png


azure-data-factory
image.png (65.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
1 Vote"
NandanHegde-7720 answered AbdullaMahammadKhanDawood-5577 commented

Hey,
The below code should help

 {
     "name": "pipeline11",
     "properties": {
         "activities": [
             {
                 "name": "Lookup1",
                 "type": "Lookup",
                 "dependsOn": [],
                 "policy": {
                     "timeout": "7.00:00:00",
                     "retry": 0,
                     "retryIntervalInSeconds": 30,
                     "secureOutput": false,
                     "secureInput": false
                 },
                 "userProperties": [],
                 "typeProperties": {
                     "source": {
                         "type": "JsonSource",
                         "storeSettings": {
                             "type": "AzureBlobStorageReadSettings",
                             "recursive": true,
                             "enablePartitionDiscovery": false
                         },
                         "formatSettings": {
                             "type": "JsonReadSettings"
                         }
                     },
                     "dataset": {
                         "referenceName": "Json3",
                         "type": "DatasetReference"
                     },
                     "firstRowOnly": false
                 }
             },
             {
                 "name": "Set variable1",
                 "type": "SetVariable",
                 "dependsOn": [
                     {
                         "activity": "Lookup1",
                         "dependencyConditions": [
                             "Succeeded"
                         ]
                     }
                 ],
                 "userProperties": [],
                 "typeProperties": {
                     "variableName": "Result",
                     "value": {
                         "value": "@activity('Lookup1').output.value[0].results",
                         "type": "Expression"
                     }
                 }
             },
             {
                 "name": "ForEach1",
                 "type": "ForEach",
                 "dependsOn": [
                     {
                         "activity": "Lookup1",
                         "dependencyConditions": [
                             "Succeeded"
                         ]
                     }
                 ],
                 "userProperties": [],
                 "typeProperties": {
                     "items": {
                         "value": "@array(activity('Lookup1').output.value[0].results)",
                         "type": "Expression"
                     },
                     "isSequential": true,
                     "activities": [
                         {
                             "name": "Append variable1",
                             "type": "AppendVariable",
                             "dependsOn": [],
                             "userProperties": [],
                             "typeProperties": {
                                 "variableName": "Id",
                                 "value": {
                                     "value": "@item().id",
                                     "type": "Expression"
                                 }
                             }
                         }
                     ]
                 }
             },
             {
                 "name": "Set variable2",
                 "type": "SetVariable",
                 "dependsOn": [
                     {
                         "activity": "ForEach1",
                         "dependencyConditions": [
                             "Succeeded"
                         ]
                     }
                 ],
                 "userProperties": [],
                 "typeProperties": {
                     "variableName": "Final",
                     "value": {
                         "value": "@variables('Id')",
                         "type": "Expression"
                     }
                 }
             }
         ],
         "variables": {
             "Result": {
                 "type": "String"
             },
             "Id": {
                 "type": "Array"
             },
             "Final": {
                 "type": "Array"
             }
         },
         "annotations": []
     }
 }




Dataset:

 {
     "name": "Json3",
     "properties": {
         "linkedServiceName": {
             "referenceName": "AzureBlobStorage1",
             "type": "LinkedServiceReference"
         },
         "annotations": [],
         "type": "Json",
         "typeProperties": {
             "location": {
                 "type": "AzureBlobStorageLocation",
                 "fileName": "users.json",
                 "container": "test"
             }
         },
         "schema": {}
     }
 }


120990-id.png



id.png (13.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks alot Nandan giving resolution for the requirment, It worked.

0 Votes 0 ·
NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered AbdullaMahammadKhanDawood-5577 commented

Hey,
Can you provide the error message and it would be great if you can paste the input json value rather than an image :)

But you can try out the below method as well which might work :

For you to pull those values, you would need a foreach activity wherein your input for foreach would be:
@array(activity('Lookup1').output.value)[0].results


within Foreach, you can use append variable
@item().id

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Nandan,

Thank you I will try this expression, However I am enclosing input json file converting it to .txt file since I am unable to upon the JSON file as an attachment.

Can you convert it again to json format, Let me know if this works. Meanwhile I will try this expression and update.

Thank you in anticipation.

Regards,
Mahammad Khan

120849-users.txt


0 Votes 0 ·
users.txt (167.7 KiB)