Nested SEARCH with JOIN

[atifsyedali]

First, thank you for this wonderful library 👍 💯

I am trying to do a JOIN on a SEARCH and I am struggling to do so. Here's a scenario:

JSON Structure

If I have a structure that looks like this:

var obj = {
    users: {
        user1: {
            id: "user1",
            projects: [
                "p1",
                "p2",
                "p3"
            ],
            teams: [
                "t4",
                "t5",
                "t6",
            ]
        },
        user2: {
            id: "user2",
            projects: [
                "p11",
                "p22",
                "p33"
            ],
            teams: [
                "t44",
                "t55",
                "t66",
            ]
        }
    },
    projects: {
        p1: {
                id: "p1",
                name: "Project 1"
        },
        p2: {
                id: "p2",
                name: "Project 2"
        },
        p3: {
                id: "p3",
                name: "Project 3"
        },
        p11: {
        id: "p11",
        name: "Project 11"
        },
        p22: {
        id: "p22",
        name: "Project 22"
        },
        p33: {
        id: "p33",
        name: "Project 33"
        }
    },
    teams: {
        t4: {
        id: "t4",
        name: "Team 4"
        },
        t5: {
        id: "t5",
        name: "Team 5"
        },
        t6: {
        id: "t6",
        name: "Team 6"
        },
        t44: {
        id: "t44",
        name: "Team 44"
        },
        t55: {
        id: "t55",
        name: "Team 55"
        },
        t66: {
        id: "t66",
        name: "Team 66"
        }
    }
};

Query 1:

I can do a search for a team or a project by doing this:

alasql("SEARCH teams / as @team WHERE(name='Team 4') RETURN (@team->id as id) FROM ?", [obj])

Which returns:

[
  {
    "id": "t4"
  }
]

Query 2

And I can also do a search for users that are part of a team or a project by doing this:

alasql("SEARCH users / as @user WHERE(teams->indexOf('t4') > -1) RETURN (@user as user) FROM ?", [obj])

Which returns:

[
  {
    "user": {
      "id": "user1",
      "projects": [
        "p1",
        "p2",
        "p3"
      ],
      "teams": [
        "t4",
        "t5",
        "t6"
      ]
    }
  }
]

Joining Manually

To run a query that says, "Give me all users that are part of a team named 'Team 4' or part of a project named 'Project 1'", I have to currently run Query 1, for each result, create a special WHERE clause for Query 2.

Search + Join

Is there a way to combine the two queries in a JOIN clause? Are JOIN clauses supported on SEARCH terms? I cannot find any documentation on such.

[mathiasrw]

Does @agershun have any inputs on this?