# MongoDB: Join and Format Array of Data

If you have an array of id and you want to retrieve data from another table and filter the properties to return you will use the **aggregate** feature of mongo and create a pipeline like so:

1. `$match` - to retrieve the document you're interested in and the list of id you want to proceed
    
2. `$lookup` - to join the other table
    
3. `$group` or `$project` - format your data
    

Let's make this simple with a real life example.

Imagine you have a Library, this library contains a list of unavailable Books.

In your Library collection you store an array of Id representing all the unavailable books for this library.

**Our objective:** retrieve all unavaible books (name and id) for a specific library.

```javascript
Library {
    id: string,
    name: string,
    unavailable_books: string[]
}

Book {
    id: string,
    title: string,
    author: string
}
```

```javascript
// Mongo playground link to play with it : 
// https://mongoplayground.net/p/2TIGfemo8g-
{
    $match: {
        id: libraryId
    }
}
{
    $lookup: {
        from: 'books', // The collection to join
        localField: 'unavailable_books', // The property containing your ids
        foreignField: 'id',
        as: 'unavailableBooks',
    },
}
```

With this query you retrieve all the properties of both collections.

### The format step

Now that we have all the data. We can use multiple approaches.

#### $group

You can create a `$group` step an use `$push` to create and array containing only the properties you want:

```javascript
//Mongo playground link: https://mongoplayground.net/p/oxE7ZQohjSE
{
    $match: {
      id: libraryId
    }
  },
  {
    $lookup: {
      from: "books",
      localField: "unavailable_books",
      foreignField: "id",
      as: "unavailableBooks"
    }
  },
  {
    $group: {
      _id: "$id",
      unavailable: {
        $push: {
          id: "$unavailableBooks.id",
          title: "$unavailableBooks.title"
        }
      }
    }
  }
```

```javascript
// Result
[
  {
    "_id": 1,
    "unavailable": [
      {
        "id": [
          1,
          3,
          4
        ],
        "title": [
          "Hamlet",
          "1984",
          "Don Quichotte"
        ]
      }
    ]
  }
]
```

But it return the book id and name in two separate array which is not what we want.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">If you don't have any data in your <code>$unavailableBooks</code> it return the same result but "unavailable" contains an object with empty array: <a target="_blank" rel="noopener noreferrer nofollow" href="https://mongoplayground.net/p/9emHOPhq2OQ" style="pointer-events: none">Mongo Playground Link</a></div>
</div>

#### $project

Using a `$project` makes things easy because you can use `$cond`

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text"><code>$cond</code> cannot be used in a <code>$group</code> step</div>
</div>

`$cond` work exactly like the if/else condition:

```javascript
$cond: {
    "if": { condition },
    "then": //Do something,
    "else": // Do something else
}
```

Now we can say: If `unavailableBooks` is empty, return `[]` otherwise push my data in "unavailable"

By using the `$map` operator instead of the `$push` :

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text"><code>$push</code> cannot be used in a <code>$project</code> step</div>
</div>

```javascript
// MongoPlayground with final result
// https://mongoplayground.net/p/GZgHMe4mZVr
{
    $match: {
      id: 1
    }
  },
  {
    $lookup: {
      from: "books",
      localField: "unavailable_books",
      foreignField: "id",
      as: "unavailableBooks"
    }
  },
  {
    $project: {
      _id: "$id",
      name: "$name",
      unavailable: {
        $cond: {
          "if": {
            $eq: [
              "unavailableBooks",
              []
            ]
          },
          "then": [],
          "else": {
            $map: {
              input: "$unavailableBooks",
              as: "d",
              in: {
                firstName: "$$d.id",
                lastName: "$$d.title"
              }
            }
          }
        }
      }
    }
  }
```

```javascript
// Result 
[
  {
    "_id": 1,
    "name": "Library 1",
    "unavailable": [
      {
        "firstName": 1,
        "lastName": "Hamlet"
      },
      {
        "firstName": 3,
        "lastName": "1984"
      },
      {
        "firstName": 4,
        "lastName": "Don Quichotte"
      }
    ]
  }
]
```

### Conclusion

This is what I've found for now. It wasn't easy to get my head around this.

I feel like using the `$project` step to do so is strange as this step is supposed to filter wich field must be return but I didn't found a solution that work with the `$group` step yet.

If you know any other method to get the same result from a `$group` let me know!