How to parse JSON using "$ref" in Azure Data Factory

Leandro 1 Reputation point
2022-04-19T07:01:41.607+00:00

Hi, I am using Azure Data Factory to get JSON data from a REST API, flatten it in a Data Flow and finally store it in our data lake.
Our API is using System.Text.Json with ReferenceHandler set to Preserve. This results in a Json like that:

{
  "$id": "1",
  "Name": "Tyler Stein",
  "Manager": null,
  "DirectReports": {
    "$id": "2",
    "$values": [
      {
        "$id": "3",
        "Name": "Adrian King",
        "Manager": {
          "$ref": "1"
        },
        "DirectReports": null
      }
    ]
  }
}

Unfortunately, when looking at the column projection in Azure Data Flow the "$ref" is listed as separate column instead of replacing it with the object it is referring to.

How can I make sure that the JSON is deserialized correctly before I continue with flattening activities etc.?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,578 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,950 questions
{count} votes