Import Multiple Payloads with Dynamic Unroll By in ADF dataflow

Question

The example payloads all have unique keys "/", "/10/101", "/10/12345/AB/100001". For each payload (Cosmos container items) my goal is to send id, item_description and delete to one SQL table and id, linktype (linktype = https://test/testIdeas), href, title, fwqs, public, type, context and hreflang to a second table, with id being the relation between both tables.

The first issue I faced was the dynamic keys "/", "/10/101" & "/10/12345/AB/100001" in each payload, these need to be dealt with in order to access the "anchor", "item description" & "linkset" information so "https://test/testIdeas" can be unrolled so I can access the values href, title, fwqs etc.

The unroll by dynamic key has been answered here: Can the Flatten activity in ADF data flow handle dynamic unroll by. However when you import more than 1 payload at a time from Cosmos (as you do during a change feed initial load) it would appear only data from the latest payload is imported and only parts from other payloads. I will add desired/actual results screen shot.

Desired results:

current results:

current results

The pipeline needs to be able to handle:

dynamic unroll by as each payload varies
then for each payload there could be one or multiple linktypes to unroll (linktype = https://test/testIdeas). Multiple linktypes in a schema could be (https://test/testIdeas & https://test/activityIdeas & https://test/contact).
Data from multiple items during a single pipeline run needs to be split correctly between the SQL tables

Ex.1

{
  "id": "01-000008",
  "pKey": "000008",
  "delete": false,
  "/": {
    "fwqs": false,
    "itemDescription": "Test Description one",
    "linkset": {
      "anchor": "Test anchor one",
      "itemDescription": "Test Description one",
      "https://test/testIdeas": [
        {
          "href": "https://test.com/test/",
          "title": "Where to test one",
          "fwqs": true,
          "public": true,
          "type": "text/html",
          "context": [
            "us"
          ],
          "hreflang": [
            "en"
          ]
        }
      ],
      "https://test/activityIdeas": [
        {
          "href": "https://test.com/test/",
          "title": "Where to buy",
          "fwqs": true,
          "public": true,
          "type": "text/html",
          "context": [
            "us"
          ],
          "hreflang": [
            "en"
          ]
        }
      ]
    }
  }
}

Ex.2

{
  "id": "01-000009",
  "pKey": "000009",
  "delete": false,
  "/10/101": {
    "fwqs": false,
    "itemDescription": "Test Description Two",
    "linkset": {
      "anchor": "Test anchor Two",
      "itemDescription": "Test Description Two",
      "https://test/testIdeas": [
        {
          "href": "https://test.com/test/",
          "title": "Where to test",
          "fwqs": true,
          "public": true,
          "type": "text/html",
          "context": [
            "us"
          ],
          "hreflang": [
            "en"
          ]
        }
      ]
    }
  }
}

Ex.3

{
  "id": "01-0000010",
  "pKey": "0000010",
  "delete": false,
  "/10/12345/AB/100001": {
    "fwqs": false,
    "itemDescription": "Test Description Three",
    "linkset": {
      "anchor": "Test anchor Three",
      "itemDescription": "Test Description Three",
      "https://test/testIdeas": [
        {
          "href": "https://test.com/test/",
          "title": "Where to test",
          "fwqs": true,
          "public": true,
          "type": "text/html",
          "context": [
            "us"
          ],
          "hreflang": [
            "en"
          ]
        }
      ]
    }
  }
}

Import Multiple Payloads with Dynamic Unroll By in ADF dataflow

0 Answers0