How to get an array of unique tickers

Working with Data
1

I have a users collection with the following structure

{
  "_id": "609f36c35931ac30173d920d",
  "firstName": "John",
  "lastName": "Doe",
  "accounts": [
    {
      "_id": "6288f730595169ab83742200",
      "bankName": "Chase",
      "snapshot": {
        "balances": [
          {
            "ticker": "USD",
            "amount": 1200,
            "usdValue": 1200
          },
          {
            "ticker": "EUR",
            "amount": 1000,
            "usdValue": 1100
          }
        ]
      }
    },
    {
      "_id": "6288f730595169ab83742201",
      "bankName": "Bank of America",
      "snapshot": {
        "balances": [
          {
            "ticker": "USD",
            "amount": 500,
            "usdValue": 500
          },
          {
            "ticker": "GBP",
            "amount": 500,
            "usdValue": 600
          }
        ]
      }
    }
  ]
},
{
  "_id": "609f36c35931ac301883492",
  "firstName": "Jane",
  "lastName": "Doe",
  "accounts": [
    {
      "_id": "6288f730595169ab83742200",
      "bankName": "Wells Fargo",
      "snapshot": {
        "balances": [
          {
            "ticker": "USD",
            "amount": 0,
            "usdValue": 0
          },
          {
            "ticker": "AUD",
            "amount": 1000,
            "usdValue": 800
          }
        ]
      }
    },
    {
      "_id": "6288f730595169ab83742201",
      "bankName": "TD Bank",
      "snapshot": {
        "balances": [
          {
            "ticker": "CAD",
            "amount": 500,
            "usdValue": 650
          },
          {
            "ticker": "HKD",
            "amount": 0,
            "usdValue": 0
          }
        ]
      }
    }
  ]
}

I want to get the following output:

["USD", "GBP", "AUD", "CAD", "HKD", "EUR"]

I’m completely new to MongoDB. Can you please help?

2

You can do it like this:

db.collection.aggregate({
  "$group": {
    "_id": "$accounts.snapshot.balances.ticker"
  }
},
{
  "$unwind": "$_id"
},
{
  "$unwind": "$_id"
},
{
  "$group": {
    "_id": null,
    "values": {
      "$addToSet": "$_id"
    }
  }
},
{
  "$project": {
    "values": 1,
    "_id": 0
  }
})

Working example

2 Likes
3

Thank you so much, that’s really overwhelming for a beginner.

1 Like
4

Kudos for the double $unwind after the $group.

Instinctively, I would have $unwind first but by doing $group first I feel it is more efficient as the working set and RAM requirement is cut right at the first stage.

I like it.

1 Like
5

Hi @steevej,

Thanks! :smile:

Yup, I also this it’s more efficient to do $group first. :smile:

6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.