Data Factory: How to flatten json hierarchy

2 answers

• answered 2022-05-04 07:49 Grekkq

  In DataFlow you can find flatten transformation which can unroll arrays. It's this cyan color in Formatters menu.

  According to this StackOverflow answer it is also possible with foreach loop but it feels hacky.

• answered 2022-05-04 12:48 wBob

  Just in case you do ever consider using Azure SQL DB's built-in abilities to work with JSON eg OPENJSON, JSON_VALUE and JSON_QUERY, here is a common pattern I use: land the data in a SQL table using Azure Data Factory (ADF) then work with it in SQL, eg:

  CREATE TABLE #tmp ( 
      jsonId INT IDENTITY PRIMARY KEY, 
      [json] NVARCHAR(MAX)
      );
  GO
  
  DECLARE @json NVARCHAR(MAX) = '{
      "customerId": 2241,
      "soidGt": null,
      "timestampGt": "2022-04-25T00:00:00",
      "timestampLt": null,
      "orders": [
          {
              "soid": 68810264,
              "id": "a4b84f56-c6a4-4b37-bffb-a34d04c513c4",
              "tableId": 4676,
              "revenueUnitId": 682,
              "lines": [
                  {
                      "solid": 147557444,
                      "articleId": 70949,
                      "quantity": 3,
                      "taxPctValue": 25,
                      "articleName": "Diavola",
                      "netAmount": 516,
                      "grossAmount": 645
                  },
                  {
                      "solid": 147557445,
                      "articleId": 70961,
                      "quantity": 1,
                      "taxPctValue": 25,
                      "articleName": "Parma ai pomodori secchi",
                      "netAmount": 183.2,
                      "grossAmount": 229
                  }
              ],
              "payments": [
                  {
                      "soptid": 70655447,
                      "paymentTypeId": 2,
                      "amount": 874
                  }
              ]
          },
          {
              "soid": 68810622,
              "id": "1b356f45-7df7-42ba-8d50-8b14cf67180d",
              "tableId": 4546,
              "revenueUnitId": 83,
              "lines": [
                  {
                      "solid": 147557985,
                      "articleId": 71159,
                      "quantity": 2,
                      "taxPctValue": 25,
                      "articleName": "Hansa 0,4L",
                      "netAmount": 152,
                      "grossAmount": 190
                  },
                  {
                      "solid": 147557986,
                      "articleId": 70948,
                      "quantity": 1,
                      "taxPctValue": 25,
                      "articleName": "Parma",
                      "netAmount": 175.2,
                      "grossAmount": 219
                  },
                  {
                      "solid": 147557987,
                      "articleId": 70918,
                      "quantity": 1,
                      "taxPctValue": 25,
                      "articleName": "Focaccia sarda",
                      "netAmount": 71.2,
                      "grossAmount": 89
                  },
                  {
                      "solid": 147557988,
                      "articleId": 70935,
                      "quantity": 1,
                      "taxPctValue": 25,
                      "articleName": "Pasta di manzo",
                      "netAmount": 196,
                      "grossAmount": 245
                  }
              ],
              "payments": [
                  {
                      "soptid": 70655798,
                      "paymentTypeId": 2,
                      "amount": 750
                  }
              ]
          }
      ]
  }'
  
  
  INSERT INTO #tmp ( json ) VALUES ( @json );
  
  -- CAST, JSON_VALUE, OPENJSON
  SELECT
      JSON_VALUE( t.[json], '$.customerId' ) customerId,
      JSON_VALUE( t.[json], '$.soidGt' ) soidGt,
      JSON_VALUE( t.[json], '$.timestampGt' ) timestampGt,
      JSON_VALUE( t.[json], '$.timestampLt' ) timestampLt,
  
      -- orders
      JSON_VALUE( o.[value], '$.soid' ) soid,
      JSON_VALUE( o.[value], '$.id' ) id,
      JSON_VALUE( o.[value], '$.tableId' ) tableId,
      JSON_VALUE( o.[value], '$.revenueUnitId' ) revenueUnitId,
  
      -- lines
      JSON_VALUE( l.[value], '$.solid' ) solid,
      JSON_VALUE( l.[value], '$.articleId' ) articleId,
      JSON_VALUE( l.[value], '$.quantity' ) quantity,
      JSON_VALUE( l.[value], '$.taxPctValue' ) taxPctValue,
      JSON_VALUE( l.[value], '$.articleName' ) articleName,
      JSON_VALUE( l.[value], '$.netAmount' ) netAmount,
      JSON_VALUE( l.[value], '$.grossAmount' ) grossAmount,
  
      -- payments
      JSON_VALUE( p.[value], '$.soptid' ) soptid,
      JSON_VALUE( p.[value], '$.paymentTypeId' ) paymentTypeId,
      JSON_VALUE( p.[value], '$.amount' ) amount
  
  FROM #tmp t
      CROSS APPLY OPENJSON( t.[json], '$.orders' ) o
          CROSS APPLY OPENJSON( o.[value], '$.lines' ) l
          CROSS APPLY OPENJSON( o.[value], '$.payments' ) p;
  

  My results:

  

  This is more of an ELT (and code-heavy) approach taking advantage of the existing compute you have running (Azure SQL DB) rather than an ETL approach, eg Mapping Data Flows (or low code) which spins up additional compute in the background. Whatever works for you. It's all managed for you but there's always more than one way to do things.