Transforming aggregate array of objects into document keys

I have a set of trade data that looks like the following

{
    "_id" : 1498290900.0,
    "trade" : {
        "type" : "Adjust",
        "data" : {
            "type" : "bid",
            "rate" : "0.00658714",
            "amount" : "3.82354427"
        },
        "date" : 1498290930291.0,
        "name" : "TLX"
    }
},{
    "_id" : 1498290900.0,
    "trade" : {
        "type" : "Adjust",
        "data" : {
            "type" : "ask",
            "rate" : "0.00658714",
            "amount" : "3.82354427"
        },
        "date" : 1498290930291.0,
        "name" : "TLX"
    }
},{
    "_id" : 1498290900.0,
    "trade" : {
        "type" : "Remove",
        "data" : {
            "type" : "ask",
            "rate" : "0.00680891"
        },
        "date" : 1498290931349.0,
        "name" : "TLX"
     }
}

These entries are from $rewind, explaining why the _id is consistent. My goal now is to group them based on their _id. So, I attempted the following

{ 
    $group: { 
      _id: {_id: "$_id", name: "$trade.type", dtype: "$trade.data.type"},
        count          : {$sum: 1}
    },
  },{$project: { _id: "$_id._id", type: "$_id.name", count: 1, dtype: "$_id.dtype" } },
  {

      $group: {
          _id: "$_id",
          results: { $push : "$$ROOT" }
          }
  }

This gave me a decent output as shown below

 {
    "_id" : 1498276800.0,
    "results" : [ 
        {
            "count" : 16.0,
            "_id" : 1498276800.0,
            "type" : "Adjust",
            "dtype" : "bid"
        }, 
        {
            "count" : 15.0,
            "_id" : 1498276800.0,
            "type" : "Remove",
            "dtype" : "bid"
        }, 
        {
            "count" : 3.0,
            "_id" : 1498276800.0,
            "type" : "Remove",
            "dtype" : "ask"
        }, 
        {
            "count" : 1.0,
            "_id" : 1498276800.0,
            "type" : "Adjust",
            "dtype" : "ask"
        }
    ]
}

However, my aim was to create an output more similar to this

  {
    "_id" : 1498276800.0,
    "Modify": {
      "bid":{
        "count": 16.0
      },
      "ask": {
        "count": 1.0
      }
    },
    "Remove": {
      "bid":{
        "count": 15.0
      },
      "ask": {
        "count": 3.0
      }
    }
  }

Despite trying various combinations with $projections, I couldn't achieve the desired output.

If anyone can guide me in the right direction, it would be greatly appreciated.

Thank you.

UPDATE

Excluding the final stage of the pipeline, here are example documents organized neatly by bid/ask per type and ready to be grouped by _id. 

{
    "_id" : {
        "_id" : 1498276800.0,
        "type" : "orderBookRemove"
    },
    "results" : [ 
        {
            "k" : "bid",
            "v" : {
                "count" : 15.0
            }
        }, 
        {
            "k" : "ask",
            "v" : {
                "count" : 3.0
            }
        }
    ]
},
{
    "_id" : {
        "_id" : 1498276800.0,
        "type" : "orderBookModify"
    },
    "results" : [ 
        {
            "k" : "bid",
            "v" : {
                "count" : 16.0
            }
        }, 
        {
            "k" : "ask",
            "v" : {
                "count" : 1.0
            }
        }
    ]
}

When the last part of the pipeline is executed, i.e., 

{ "$group": {
    "_id": "$_id._id",
    "results": {
      "$push": {
        "k": "$_id.type",
        "v": "$results"
      }
    }
  }}

I only receive the first 'bid' element of the results array. The second item 'ask' seems to disappear?

{
    "_id" : 1498280700.0,
    "results" : [ 
        {
            "k" : "orderBookRemove",
            "v" : [ 
                {
                    "k" : "bid",
                    "v" : {
                        "count" : 9.0
                    }
                }
            ]
        }, 
        {
            "k" : "orderBookModify",
            "v" : [ 
                {
                    "k" : "bid",
                    "v" : {
                        "count" : 6.0
                    }
                }
            ]
        }
    ]
}
javascriptmongodbaggregation-framework

Answer №1

The effectiveness of the solution hinges on the MongoDB version at your disposal, or perhaps not entirely, depending on perspective. Given that the data initially emanated from an array format, I will proceed in that structure and handle each option accordingly.

Let's consider the source:

{
        "_id" : ObjectId("594f3a530320738061df3eea"),
        "data" : [
                {
                        "_id" : 1498290900,
                        "trade" : {
                                "type" : "Modify",
                                "data" : {
                                        "type" : "bid",
                                        "rate" : "0.00658714",
                                        "amount" : "3.82354427"
                                },
                                "date" : 1498290930291,
                                "name" : "TLX"
                        }
                },
                {
                        "_id" : 1498290900,
                        "trade" : {
                                "type" : "Modify",
                                "data" : {
                                        "type" : "ask",
                                        "rate" : "0.00658714",
                                        "amount" : "3.82354427"
                                },
                                "date" : 1498290930291,
                                "name" : "TLX"
                        }
                },
                {
                        "_id" : 1498290900,
                        "trade" : {
                                "type" : "Remove",
                                "data" : {
                                        "type" : "ask",
                                        "rate" : "0.00680891"
                                },
                                "date" : 1498290931349,
                                "name" : "TLX"
                        }
                }
        ]
}

MongoDB 3.4

To achieve the desired outcome, utilize $replaceRoot and $arrayToObject, ensuring meticulous placement of results:

db.dtest.aggregate([
  { "$unwind": "$data" },
  { "$group": { 
   "_id": {
     "_id": "$data._id", 
     "type": "$data.trade.type",
     "dtype": "$data.trade.data.type"
   },
   "count": { "$sum": 1 }
  }},
  { "$group": {
    "_id": {
      "_id": "$_id._id",
      "type": "$_id.type"
    },
    "results": {
      "$push": {
        "k": "$_id.dtype",
        "v": {
          "count": "$count"
        }
      }         
    }
  }},
  // Additional pipeline stages
])

All versions

In most scenarios, it is more practical to retain the aggregated array form and handle the transformation on the client side. There often isn't a necessity for further aggregation since this step has already been completed.

Simple implementations in various languages can be executed, with basic JavaScript concepts applicable even in the shell environment:

db.dtest.aggregate([
  { "$unwind": "$data" },
  { "$group": { 
   "_id": {
     "_id": "$data._id", 
     "type": "$data.trade.type",
     "dtype": "$data.trade.data.type"
   },
   "count": { "$sum": 1 }
  }},
  // More pipeline stages
]).map(doc => 
  doc.results.map(r => 
    ({ k: r.k, v: r.v.reduce((acc,curr) =>
      Object.assign(acc, { [curr.k]: curr.v }),{}) 
    })
  ).reduce((acc,curr) => 
    Object.assign(acc, { [curr.k]: curr.v }),{ _id: doc._id })
)

Essentially, this method accomplishes the same objective as the advanced operators, but simplifies the process by processing the cursor similarly to the new pipeline stage within each document.

Thus, unless you plan to conduct further aggregation beyond this point, utilizing these state-of-the-art operators is largely unnecessary. Achieving the same outcome requires fewer lines of code and offers greater simplicity in expression.

Both methods yield identical outcomes:

    {
            "_id" : 1498290900,
            "Modify" : {
                    "ask" : {
                            "count" : 1
                    },
                    "bid" : {
                            "count" : 1
                    }
            },
            "Remove" : {
                    "ask" : {
                            "count" : 1
                    }
            }
    }

Debug - to remove

Upon employing the provided update information, following this process should yield the anticipated output:

db.test.aggregate([
  { "$group": {
    "_id": "$_id._id",
    // Additional steps
  }}
 ])

When assessing the obtained result against your specified output, discrepancies arise, indicating deviation from the prescribed example.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Transform my Curl script into a NodeJS app

I'm trying to replicate the functionality of a curl command within my NodeJS application, but I am facing some difficulties. Any guidance on how to achieve this would be greatly appreciated. curl -H "Authorization: bearer $TOKEN" If I already hav ...

javascriptnode.jscurlrequest

Click the ng-focus button to focus the textarea

I am in need of a solution where I can trigger a button within the ng-repeat loop and focus on a textarea specific to that item in the loop. HTML <div ng-repeat="item in items"> <textarea focus-when="showTextarea">{{ item }}</textarea> ...

javascripthtmlangularjsangularjs-ng-repeat

Difficulty loading AngularJS 1.3 page on Internet Explorer 8

Being an avid user of Angular, it pains me to even bring up the topic of IE8, a browser that many consider to be pure evil and deserving of extinction. Despite my reservations, I am experiencing difficulties with loading Angular 1.3 in IE8. The page break ...

javascriptangularjsinternet-explorer-8

JavaScript - Utilizing an image file in relation to a URL pathway

Is there a way to reference an image URL using a relative path in a JavaScript file similar to CSS files? To test this, I created two divs and displayed a gif in the background using CSS in one and using JavaScript in the other: -My file directory struct ...

javascriptcss

Can the meta viewport be eliminated using a script?

I currently do not have access to the HTML file on the server side, but I can make changes by adding additional HTML to the website. My issue is with a non-responsive site listed below, which now requires me to zoom in after it loads due to the new viewpor ...

javascriptjquerycssviewport

Activate a function for a targeted ajax request within a global $.ajax event

I have a series of 3-4 ajax calls that are scheduled to be executed at some point. In one of these calls, I want to activate a function on the global ajaxSend event. This particular ajax call may not be the first or last in the sequence. However, when I at ...

javascriptjqueryajax

AngularJS - Utilizing Google's Place Autocomplete API Key

Recently, I started digging into Google's APIs and attempting to integrate the Places Autocomplete API with Angular. Although I'm fairly new to autocomplete features in general, I haven't included the jQuery library in my project yet. I&apos ...

javascriptjqueryangularjsautocompletegoogle-places-api

Performing real-time computations on a generated field within MongoDB queries

I am looking to dynamically generate a field in my MongoDB database that will be a hash of certain other fields. The goal is to avoid manual updates to the hash whenever one of the dependent fields changes, as well as offload the hashing computation from m ...

mongodbmapreducemongodb-queryaggregation-framework

Flask does not provide a direct boolean value for checkboxes

After struggling for a week, I am still lost on where to make changes in my code. I need the checkbox to return a boolean value in my Flask application. Below are snippets of the relevant code: mycode.py import os, sqlite3 from flask import Flask, flash ...

javascriptpythonhtmlcssflask

JavaScript Character Set on the Dynamic Page in Delphi

Within my Delphi application, I am dynamically generating HTML content. Displaying UTF-8 encoded strings in the webpage body is not an issue for me as I use HTMLEscape to encode regular strings (ensuring all strings in the list are properly escaped). The ...

javascripthtmldelphiutf-8delphi-xe

Problem arising from apostrophe usage in Javascript OData request

In my current project, I have a text input field that passes a value through JS to fetch filtered data of names from a JSON file using OData query parameters. However, I've encountered an issue where if a name contains an apostrophe, it results in a ...

javascriptjsonajaxodata

Material-UI and TypeScript are having trouble finding a compatible overload for this function call

Currently, I'm in the process of converting a JavaScript component that utilizes Material-ui to TypeScript, and I've encountered an issue. Specifically, when rendering a tile-like image where the component prop was overridden along with an additi ...

javascriptreactjstypescript

Only one JSON file is handled at a time, no duplicates are made

We start by utilizing the powerful D3 JavaScript library for initializing data documents, followed by creating a custom JavaScript script to handle data processing. An excerpt from the customized JavaScript script appears as follows: drawLegend(); ...

javascriptjson

What is the method for writing to an HTML file with the use of expressjs?

Alright, I have an interesting idea here. I am looking for a way to allow users to push a button and have a new p element permanently added to the HTML file. This means that even after reloading the page, everyone should be able to see this new element. An ...

javascripthtmlcssnode.jsexpress

Consecutive pair of XMLHttpRequest requests

Is it possible to create a functionality where the page index.php sends a Javascript variable called idToken, which is then received in another page called token.php using Javascript as well? In the token.php page, there will be more code that processes ...

javascriptphpajax

Updating the border color in jQuery can be done without affecting the `border-left-color`

I'm working with an element that has the following CSS properties: border: 20px solid; border-color:#4ea88e; border-right-width: 10px; border-left-color: transparent; I need to change the border-color using JavaScript without affecting the border-l ...

javascriptjquerycss

"Incorporate an image into the data of an AJAX POST request for a web service invocation

I have been attempting (with no success thus far) to include an image file in my JSON data when making a call to a method in my webservice. I have come across some threads discussing sending just an image, but not integrating an image within a JSON data o ...

javajavascriptajaxjsonweb-services

Studio 3T chooses all elements within an array column

I have a database collection stored in Mongo with the following schema (simplified): { "_id" : ObjectId("55a94615a243a426db43d81e"), "property_name" : "My Main Property", "domain_list" : [ "mynumber1url.com", "mynumber2url.c ...

mongodbstudio3t

How can a TypeScript Angular directive utilize a function?

Recently, I have been following a unique Angular directive TypeScript pattern that I find really effective. The approach involves giving the directive its own isolated scope by creating a new controller. I encountered a scenario where I needed to invoke a ...

javascriptangularjsangularjs-directivetypescript

Are there any nodeJS template engines similar to C#'s RazorEngine?

Is there a template engine for Node.js that is similar to RazorEngine, specialized in generating HTML but also capable of handling other types of output? I am looking for a tool that can dynamically create JavaScript files along with HTML content like Razo ...

c#javascriptnode.js