maybe my searching is poor, but I couldn't find this question or answer anywhere.

Suppose I have a table CLASSROOM like:

```
[ { teacher_id: T1,
    students: [S11, S12, S13]},
  { teacher_id: T2,
     students: [S21, S22, S23]}]

```

The "students" field is an array of student\_id's. There is also a table STUDENTS like:

```
[ { id: S11, name: "Aaron"}, { id: S12, name: "Bob"}, { id: S13, name: "Charlie"},
  { id: S21, name: "Amy"}, { id: S22, name: "Becky"}, { id: S23, name: "Cat"} ]

```

I want to create the output table which has rows like:

```
[ { teacher_id: T1,
    students: [S11, S12, S13 ],
    names: [ "Aaron", "Bob", "Charlie" ] },
  { teacher_id: T2,
    students: [S21, S22, S23 ],
    names: [ "Amy", "Becky", "Cat" ] } ]

```

(Yes, this example is silly, but I don't want to bore you with my case.)

I suppose I could FLATTEN the CLASSROOM table, then do a straight join, but my real table is large & complicated enough that I want to avoid it if I can. Is there a better way?

Note: assume students can be in multiple classes. Teachers (`teacher_id`) are unique.