How to add a comma between JSON sections in Azure Data Factory

Question

Hi everyone,

I'm writing today to ask for help regarding an Excel to JSON transformation that I'm trying to automate through Azure Data Factory.

I created a dataflow in Azure Data Factory the takes an Excel file in input, filters the columns and returns a JSON in output.

The dataflow steps are:

Excel -> mapDrifted -> derivedColumn (to generate the nested JSON sections) -> aggregate -> sink (to JSON)

The output JSON I get is:

{
    "referenceNumber": "62",
    "firstName": "John",
    "lastName": "Doe",
    "gender": "male",
    "items": [
        {
            "itemNumber": "1"
        },
        {
            "itemNumber": "2"
        },
        {
            "itemNumber": "3"
        }
    ]
}
{
    "referenceNumber": "63",
    "firstName": "Jane",
    "lastName": "Doe",
    "gender": "male",
    "items": [
        {
            "itemNumber": "1"
        },
        {
            "itemNumber": "2"
        },
        {
            "itemNumber": "3"
        }
    ]


}

As you might notice, it is missing a comma (,) between the two main parts, and square brackets ([ ]) to surround the whole file. The output I'm trying to achieve would be:

[
  {
    "referenceNumber": "62",
    "firstName": "John",
    "lastName": "Doe",
    "gender": "male",
    "items": [
        {
            "itemNumber": "1"
        },
        {
            "itemNumber": "2"
        },
        {
            "itemNumber": "3"
        }
    ]
  },
  {
    "referenceNumber": "63",
    "firstName": "John",
    "lastName": "Strive",
    "gender": "male",
    "items": [
        {
            "itemNumber": "1"
        },
        {
            "itemNumber": "2"
        },
        {
            "itemNumber": "3"
        }
    ]
  }
]

Note: I added the new characters (comma and square brackets) in new lines to make it clear, but they can be inline as well.

For example, it would be great to have this kind of control for the output JSON and not only for the input. That way, I could select the arrayOfObjects option to solve this issue.

While the documentation says that it's possible in the sink step to select the filePattern and configure it as arrayOfObjects, I don't see it there.

Do you know how to achieve that?

Thank you and have a nice day! :)

Kind regards

Answer 1

Hi Marco Cavalazzi ,

Thankyou for using Microsoft Q&A platforrm and thanks for posting your question here.

As I understand your query, you want to convert the multiple jsons into single array of json output using mapping data flow after transforming tabular data into json data. Please let me know if that is not the ask here.

Although the expected output is an array of json, it would require a column to hold that value , so you might achieve the following output via the below solution, on top of which you can use set variable activity after dataflow activity to substring the unwanted part i.e. {"finaloutput": and }

{
  "finaloutput": [
    {
      "referenceNumber": "63",
      "firstName": "Jane",
      "lastName": "Doe",
      "gender": "Female",
      "items": [
        {
          "itemNumber": "1"
        },
        {
          "itemNumber": "2"
        },
        {
          "itemNumber": "3"
        }
      ]
    },
    {
      "referenceNumber": "62",
      "firstName": "John",
      "lastName": "Doe",
      "gender": "male",
      "items": [
        {
          "itemNumber": "1"
        },
        {
          "itemNumber": "2"
        },
        {
          "itemNumber": "3"
        }
      ]
    }
  ]
}

Add another aggregate transformation to create a new column named 'finaloutput' and use the below expression:

collect(@(referenceNumber=referenceNumber,firstName=firstName,lastName=lastName,gender=gender,items=items))

Here is how the output looks like:

Kindly check the following thread for more details:

https://learn.microsoft.com/en-us/answers/questions/601502/convert-json-objets-to-array-using-azure-data-flow

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou