MongoDB Aggregate Listing by Arrays Value

Question

How its making listing order by records array's value with subquery I want best query for perform in mongo side models.js

new ms.Schema({
        name : {type: String,required: true,unique:true},
        display_name: {type: String,required: true,unique:true},
        url: {type: String,default:'' },
        icon: {type: String,default :'no.png' },
        assets : {type:Array,default : ['BTCUSDT']},
        active: {type: Boolean, default : true},
    })

mongodb's record goes here

{
    "_id" : ObjectId("5e9e78c477b1c7a1bfc4978c"),
    "url" : "https://bitso.com/",
    "active" : false,
    "name" : "bitso",
    "display_name" : "Bitso",
    "icon" : "Bitso.png",
    "__v" : 0,
    "seq" : 888,
    "assets" : [
        "BTCUSDT",
        "ETHUSDT",
        "LTCUSDT"
    ]
},
{
    "_id" : ObjectId("5e9e78c377b1c7a1bfc4978a"),
    "url" : "https://www.fybsg.com/",
    "active" : false,
    "name" : "fybsg",
    "display_name" : "FYB-SG",
    "icon" : "FYB-SG.png",
    "__v" : 0,
    "seq" : 888,
    "assets" : [
        "BTCUSDT",
        "ETHUSDT"
    ]
},
{
    "_id" : ObjectId("5e9e78c377b1c7a1bfc49789"),
    "url" : "https://hitbtc.com/",
    "active" : true,
    "name" : "hitbtc",
    "display_name" : "Hitbtc",
    "icon" : "Hitbtc.png",
    "__v" : 0,
    "seq" : 99,
    "assets" : [
        "BCCUSDT"
    ]
},
{
    "_id" : ObjectId("5e9e78c077b1c7a1bfc49787"),
    "url" : "https://blockchain.io/",
    "active" : false,
    "name" : "blockchainio",
    "display_name" : "Blockchain.io",
    "icon" : "Blockchain.io.png",
    "__v" : 0,
    "seq" : 999,
    "assets" : [
        "BTCUSDT",
        "ETHUSDT"
    ]
},

db.markets.aggregate([...])

if possible How can i export result like here from mongo records; Else what algorithm must i use

i need data like this

[
BTCUSDT : { which record assets have BTCUSDT listings },
ETHUSDT : { which record assets have BTCUSDT  listings},  
...

]

Mikhail Leliakin · Accepted Answer · 2020-07-11 20:35:11Z

You need to flat assets array of each doc using $unwind stage and after that just group them by this field:

db.markets.aggregate([
  {
    $unwind: "$assets"
  },
  {
    $group: {
      _id: "$assets",
      recordId: {
        $push: "$_id"
      }
    }
  }
])

You output will be:

[
  {
    "_id": "BCCUSDT",
    "recordId": [
      "5e9e78c377b1c7a1bfc49789"
    ]
  },
  {
    "_id": "BTCUSDT",
    "recordId": [
      "5e9e78c477b1c7a1bfc4978c",
      "5e9e78c377b1c7a1bfc4978a",
      "5e9e78c077b1c7a1bfc49787"
    ]
  },
  {
    "_id": "LTCUSDT",
    "recordId": [
      "5e9e78c477b1c7a1bfc4978c"
    ]
  },
  {
    "_id": "ETHUSDT",
    "recordId": [
      "5e9e78c477b1c7a1bfc4978c",
      "5e9e78c377b1c7a1bfc4978a",
      "5e9e78c077b1c7a1bfc49787"
    ]
  }
]

If it's more convinient for you to get only one object as the result, you can additionally group them by null id, store all the docs in array with k, v properties and replace the root of the single document:

  {
    $group: {
      _id: null,
      result: {
        $push: {
          k: "$_id",
          v: "$recordId"
        }
      }
    }
  },
  {
    $addFields: {
      result: {
        $arrayToObject: "$result"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: "$result"
    }
  }

Result for whole query will be:

[
  {
    "BCCUSDT": [
      "5e9e78c377b1c7a1bfc49789"
    ],
    "BTCUSDT": [
      "5e9e78c477b1c7a1bfc4978c",
      "5e9e78c377b1c7a1bfc4978a",
      "5e9e78c077b1c7a1bfc49787"
    ],
    "ETHUSDT": [
      "5e9e78c477b1c7a1bfc4978c",
      "5e9e78c377b1c7a1bfc4978a",
      "5e9e78c077b1c7a1bfc49787"
    ],
    "LTCUSDT": [
      "5e9e78c477b1c7a1bfc4978c"
    ]
  }
]

Collectives™ on Stack Overflow

MongoDB Aggregate Listing by Arrays Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related