How do I join nested data

Sorry for the basic question, I just cannot seem to figure this one out.

I'm joining two tables, person table and team table. The team table looks like this:

[
  {
    "id": 19587,
    "name": "Engineering Team",
    "persons": [
      355843,
      355844,
      354038,
      392243
    ],
    "created_by": 353118,
    "updated_by": 353118,
    "created_at": "2021-02-17T13:30:05Z",
    "updated_at": "2021-10-04T13:44:18Z"
  },

The person table looks like this:

[
  {
    "id": 443448,
    "first_name": "Frank",
    "last_name": "Miller",
    "user_type": "ADMIN",

I need to join team.persons.id to people.id

I'm struggle to write the join on this one?

Hi @bsteiger,

You can potentially use Query JSON with SQL resource then have the following query:

SELECT
  // Use a wildcard here (*) or specify each column you want to display.
FROM {{teamTable.data}} tt
JOIN {{personTable.data}} pt ON pt.id IN tt.persons

For joining two tables, it would be ideal to use the output from the resource (apiResource.data or mysqlResource.data) rather than the data placed on the table component.

Let me know if this works for you. Setting your expectations here that a person may be repeated if they belong to multiple teams.

Regards