Azure Question posted in
The official documentation to get you started can be found here.

Azure Data Factory Expression Builder: Error Structuring JSON with Nested Arrays and Objects

I’ve been working on this for a couple of days now and am reaching out for assistance. I was unable to structure the format properly and need help.

I’ve been trying to create a JSON structure in Azure Data Factory (ADF) using the Expression Builder, but I’m encountering an error. Here’s the JSON structure I want to achieve:

{
  "entity_id": "value1",
  "link_id": "value2",
  "url": "value3",
  "actions": [
    {
      "appointment_info": {}
    }
  ]
}

In the Expression Builder, I structured it as follows:

@(entity_id=entity_id,
  link_id=link_id,
  url=url,
  actions=[@(appointment_info=@())])

However, I receive an error at the line actions=[@(appointment_info=@())], specifically at @() right after appointment_info=. This should work, but it’s still showing an error in ADF, and I’m unable to structure it as intended:

"actions": [
  {
    "appointment_info": {}
  }
]

Can someone help me understand what I’m doing wrong and how to correctly format this JSON structure in ADF’s Expression Builder? Any guidance would be greatly appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In the Expression Builder, I structured it as follows:

    @(entity_id=value1,
    link_id=value2,
    url=value3,
    actions=[@(appointment_info=@(appointment_null=appointment_null))])

    I added a new column in the source's select query and fetched a null value. Since the column has null for all rows, it will always display as {}. However, this won't work for an empty string.

    select '' appointment_empty, null appointment_null from .........

    appointment_empty would display the column name and appointment_null would'nt.


  2. 0

    However, I receive an error at the line actions=[@(appointment_info=@())], specifically at @() right after appointment_info=

    I have tried the same expression, and it is saying incorrect parsing error. It seems like ADF dataflow derived column expression doesn’t support empty objects.

    If you have access to Blob or Gen2 storage account, you can try the below workaround to achieve the requirement.

    First use the below expression in the derived column transformation.

    replace(toString(@(entity_id=entity_id,
link_id=link_id,
url=url,
actions=[@(appointment_info='my_str')])),'"my_str"','{}')

    As it doesn’t support empty object, the above expression first generating required JSON with "my_str" string in the place of {}. Then replace it by {} and get the JSON string in a column.

    enter image description here

    Next, use select transformation and remove the extra columns.

    enter image description here

    In the dataflow sink, take a delimited text dataset and give the below configurations. This dataset should be from Gen2 or blob.

    enter image description here

    Don’t give any file name for this in the dataset. Give filename as filename.json in the dataflow sink settings.

    enter image description here

    Here, we are using the delimited text dataset to generate the JSON file from the JSON string that we created. As we unchecked First row as header, it will give the JSON file as expected.

    Result file:

    enter image description here

    But in the later usage of this file in ADF, you need to create another JSON dataset pointing to this location.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search