locked
CopyData error >> Array elements can only be selected using an integer index RRS feed

  • Question

  • Hello there,

    I am trying to copy JSON data into an array to a SQL table. After doing the field mapping including @activity() output fields from a WebActivity. But I face an error message I still do not understand:

    { "errorCode": "InvalidTemplate", "message": "The expression 'activity('WebActivity1').output.contacts._id' cannot be evaluated because property '_id' cannot be selected. Array elements can only be selected using an integer index.", "failureType": "UserError", "target": "Copy Data1" }

    Do I need to build an index of my JSON array? How can I handle it in ADF?

    Best,

    Tuesday, July 2, 2019 1:15 PM

All replies

  • Hello TomGoirand and thank you for your inquiry.  It sounds like 'contracts' is an array of objects, and '_id' is a member of those objects.  Without seeing a sample of your JSON I cannot be sure.

    If that is the case, you may need to loop through the array and write each item.  If there is only one item in the array, then the expression can be modified to:
    activity('WebActivity1').output.contacts[0]._id

    If this does not help, could you please share more details ?

    Wednesday, July 3, 2019 12:51 AM
  • Hi there, 

    You are guessing right!

    Please can you give me furthuer information about how to make a loop and copy each contact in the array?

    Here is the JSON sample:

    {
      "contacts": [
        {
          "_id": "5d1b41dc61fd4e1b2f959b25",
          "first_name": "",
          "last_name": "",
          "email": "",
          "phone": null,
          "status": "new",
          "lang": "en",
          "city": null,
          "country": null,
          "source": "manual",
          "role": "",
          "linkedinurl": "",
          "education_diploma": null,
          "education_school": null,
          "education_major": null,
          "company_name": "",
          "company_industry": null,
          "company_size": null,
          "unsubscribed": false,
          "customKeywords": "",
          "lastEmailCategory": null,
          "vars": {},
          "userId": {
            "_id": "5cd53c878bbdcb6ad9be4a17",
            "first_name": "Bastien",
            "last_name": "Doisneau",
            "groupId": "5b0fe5e28341880005e07a19",
            "companyId": "5b0fe5e28341880005e07a18"
          },
          "listId": {
            "_id": "5c99f69034da733c7a370cbb",
            "list_name": "mickael.oisel@econocom.com"
          }
        },
        {
          "_id": "5d1b1c8261fd4e1b2f9567d8",
          "first_name": "",
          "last_name": "",
          "email": "XXX"
          "phone": null,
          "status": "",
          "lang": "en",
          "city": null,
          "country": null,
          "source": "",
          "role": "",
          "linkedinurl": "",
          "education_diploma": null,
          "education_school": null,
          "education_major": null,
          "company_name": "",
          "company_industry": null,
          "company_size": null,
          "unsubscribed": false,
          "customKeywords": "",
          "lastEmailCategory": null,
          "vars": {},
          "userId": {
            "_id": "5cd53c878bbdcb6ad9be4a17",
            "first_name": "Bastien",
            "last_name": "Doisneau",
            "groupId": "5b0fe5e28341880005e07a19",
            "companyId": "5b0fe5e28341880005e07a18"
          }
      ],
      "page": {
        "page": 1,
        "pages": 839,
        "total": 20970
      }
    }

    Wednesday, July 3, 2019 10:11 AM
  • You can use the ForEach activity to iterate through activity('WebActivity1').output.contacts. Within each loop, you can use  @item()._id to access the '_id' field of each element
    Wednesday, July 3, 2019 8:55 PM
  • If you do not want to use a loop, you could have an App write this to a blob, then have an ADF pipeline use copy activity JSON parsing feature to send it to the SQL table.
    Friday, July 5, 2019 7:25 PM
  • Please let me know if you need any more assistance.
    Friday, July 5, 2019 7:26 PM