N1QL nested array

{
  "class": {
    "teacher": [
      {
        "name": "Bob White",
        "class": "ENG 101"
      },
      {
        "name": "Lisa Brown",
        "class": "ENG 102"
      }
    ],
    "student": [
      {
        "name": "Alex",
        "class": "ENG 101",
        "GPA": "3"
      },
      {
        "name": "Newton",
        "class": "ENG 102",
        "GPA": "6"
      },
      {
        "name": "Mai",
        "class": "ENG 101",
        "GPA": "5"
      },
      {
        "name": "Royal",
        "class": "ENG 101",
        "GPA": "6"
      },
      {
        "name": "Noah",
        "class": "ENG 102",
        "GPA": "3"
      }
    ]
  }
}

How can I use nested array to return teacher name and the list of student in his class?

Two UNNESTs, a GROUP BY, and an ARRAY_AGG(). See docs for these.

Is there other way without using ARRAY_AGG()?
Iā€™m trying to figure out how to use NEST. But it looks like impossible.

NEST requires foreign key / primary key relationships. No reason to avoid ARRAY_AGG(). It is a good function.

1 Like