Add parent JSON key values to column when using record pointer to nested array

[ziggekatten]

Let us start with the JSON response from API:

  {
      "client": "Acme Inc",
      "host": "scooby",
      "category": "salt.localgroups",
      "from": "2025-02-18T16:27:21.066+01:00",
      "asOf": "2025-02-18T16:27:21.066+01:00",
      "data": [
        {
          "group": "Operators",
          "members": [
            "Ed",
            "Jonas",
            "Harry"
          ]
        },
        {
          "group": "Administrators",
          "members": [
            "Bob",
            "Alice",
            "Zeke"
          ]
        },
        {
          "group": "Users",
          "members": [
            "Ken",
            "Barbie",
            "Donald"
          ]
        }
      ]
    }

Now, my challenge here is how to normalize this before pushing it into an Postgres table, where I would have rows containing fields from the root of the object (client and host) attached to the values in the "data" array. In effect my final data should look like this:

client	host	group	user
Acme Inc	scooby	Operators	Ed
Acme Inc	scooby	Operators	Jonas
Acme Inc	scooby	Operators	Henry
Acme Inc	scooby	Administrators	Bob
Acme Inc	scooby	Administrators	Alice
Acme Inc	scooby	Administrators	Zeke
Acme Inc	scooby	Users	Ken
Acme Inc	scooby	Users	Barbie
Acme Inc	scooby	Users	Donald

Is it even feasible to do this in the UI Builder?

[airbyte-support-bot]

Hi! This discussion hasn't had activity in over a year. Is this still relevant to you? If so, please reply and we'll keep it open. If we don't hear back within 14 days, we'll close this as part of our housekeeping. You can always reopen or create a new discussion later. Thanks!

---

Devin session