query_offered_tokens.json

{
  "v": 3,
  "q": 
  {
    "db": ["c", "u"],
    "aggregate": [
      {"$match":{
        "in.h0": "45584348",
        "in.b1.op": 82,
        "slp.valid": true
      }},
      {"$addFields": {
        "txHash": {"$concat": ["$tx.h", ":1"]}
      }},
      {"$lookup": {
        "from": "utxos",
        "localField": "txHash",
        "foreignField": "utxo",
        "as": "foundUtxo"
      }},
      {"$addFields": {
        "exchInput": {
          "$arrayElemAt": [
            {"$filter": {
              "input": "$in",
              "as": "input",
              "cond": {
                "$and": [
                  {"$eq": ["$$input.b0", "RVhDSA=="]},
                  {"$eq": ["$$input.b1.op", 82]}
                ]
              }
            }},
            0
          ]
        },
        "hasUtxo": {
          "$size": "$foundUtxo"
        }
      }},
      {"$sort": {
        "hasUtxo": -1,
        "blk.t": -1
      }},
      {"$group": {
        "_id": "$slp.detail.tokenIdHex",
        "numberOfOpenOffers": { "$sum": "$hasUtxo" },
        "numberOfClosedOffers": { "$sum": {"$subtract": [1, "$hasUtxo"] }},
        "slp": { "$first": "$slp" },
        "lastTrade": {"$first": {
          "timestamp": "$blk.t",
          "txHash": "$txHash",
          "price": "$exchInput.b3",
          "isAccepted": {"$ne": ["$foundUtxo", []]}
        }}
      }}
    ]
  }
}