N1QL nested array

SQL++
1 
{
  "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?

2

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

3

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

4

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

1 Like