Get latest value in mongodb nested documents

Question

0.00/5 (No votes)

See more:

Hi everyone.
I'm trying to figure out how can I extract the latest value of say a variable in a set of documents.
Let's say I have 2 documents:

{ 
   "Name": "doc1", 
   "Date": ISODate("2019-12-31T00:00:00.000Z"),
   "Values" : [
         {
            "VariableName": "VAR1",
            "VariableValue": "VAL1"
         },
         {
            "VariableName": "VAR2",
            "VariableValue": "VAL2"
         } ]
},
{ 
   "Name": "doc2", 
   "Date": ISODate("2020-01-31T00:00:00.000Z"),
   "Values" : [
         {
            "VariableName": "VAR1",
            "VariableValue": "VAL3"
         },
         {
            "VariableName": "VAR3",
            "VariableValue": "VAL3"
         } ]
}

I'ld like to write a "query" that extract only the latest value of each variable:

{ 
   "Document": "doc2", 
   "Date": ISODate("2020-01-31T00:00:00.000Z"),
   "VariableName", "VAR1",
   "VariableValue", "VAL3"
},
{
   "Document": "doc1", 
   "Date": ISODate("2019-12-31T00:00:00.000Z"),
   "VariableName", "VAR2",
   "VariableValue", "VAL2"
},
{ 
   "Document": "doc2", 
   "Date": ISODate("2020-01-31T00:00:00.000Z"),
   "VariableName", "VAR3",
   "VariableValue", "VAL3"
}

I'm stuck at the join stage of the pipeline when I have to join the original table with the greatest date values.

Can somebody help please?

What I have tried:

//Prepare documents
var o = [
{ 
   "Name": "doc1", 
   "Date": ISODate("2019-12-31T00:00:00.000Z"),
   "Values" : [
         {
            "VariableName": "VAR1",
            "VariableValue": "VAL1"
         },
         {
            "VariableName": "VAR2",
            "VariableValue": "VAL2"
         } ]
},
{ 
   "Name": "doc2", 
   "Date": ISODate("2020-01-31T00:00:00.000Z"),
   "Values" : [
         {
            "VariableName": "VAR1",
            "VariableValue": "VAL3"
         },
         {
            "VariableName": "VAR3",
            "VariableValue": "VAL3"
         } ]
}
];

//Insert objects
db.getCollection("docs").insertMany(o);

//Flattening I get
db.docs.aggregate(
[
   { "$unwind" : "$Values" },
   { "$project": { "Name": 1, "Date" : 1, "VariableName": "$Values.VariableName", "VariableValue": "$Values.VariableValue" } }
]
).pretty();

{
        "_id" : ObjectId("5e96d8632f441e3738a0ff2f"),
        "Name" : "doc1",
        "Date" : ISODate("2019-12-31T00:00:00Z"),
        "VariableName" : "VAR1",
        "VariableValue" : "VAL1"
}
{
        "_id" : ObjectId("5e96d8632f441e3738a0ff2f"),
        "Name" : "doc1",
        "Date" : ISODate("2019-12-31T00:00:00Z"),
        "VariableName" : "VAR2",
        "VariableValue" : "VAL2"
}
{
        "_id" : ObjectId("5e96d8632f441e3738a0ff30"),
        "Name" : "doc2",
        "Date" : ISODate("2020-01-31T00:00:00Z"),
        "VariableName" : "VAR1",
        "VariableValue" : "VAL3"
}
{
        "_id" : ObjectId("5e96d8632f441e3738a0ff30"),
        "Name" : "doc2",
        "Date" : ISODate("2020-01-31T00:00:00Z"),
        "VariableName" : "VAR3",
        "VariableValue" : "VAL3"
}

//Then I need to find latest value bye date and VariableName
db.docs.aggregate(
[
   { "$unwind" : "$Values" },
   { "$project": { "Name": 1, "Date" : 1, "VariableName": "$Values.VariableName", "VariableValue": "$Values.VariableValue" } },
   { "$group": { _id : { "VariableName": "$VariableName"}, latest: {"$max" : "$Date"} } }
]
).pretty();

{
        "_id" : {
                "VariableName" : "VAR3"
        },
        "latest" : ISODate("2020-01-31T00:00:00Z")
}
{
        "_id" : {
                "VariableName" : "VAR2"
        },
        "latest" : ISODate("2019-12-31T00:00:00Z")
}
{
        "_id" : {
                "VariableName" : "VAR1"
        },
        "latest" : ISODate("2020-01-31T00:00:00Z")
}

Posted 15-Apr-20 0:09am

coccide

Updated 15-Apr-20 3:05am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

coccide · Answer 1 · 2020-04-15T03:06:00

Ok I managed to get what I want using some intermediate view objects but I think there should be a more elegant way to do that:

var tutti = db.createView(
"tutti",
"docs",
[
   { "$unwind" : "$Values" },
   { "$project": { "Name": 1, "Date" : 1, "VariableName": "$Values.VariableName", "VariableValue": "$Values.VariableValue" } }
]
);

db.ultimeModifiche.drop();
var ultimeModifiche = db.createView(
"ultimeModifiche",
"tutti",
[
   { "$group": { _id : { "VariableName": "$VariableName"}, latest: {"$max" : "$Date"} } },
   { "$project": { "VariableName": "$_id.VariableName", "latest": 1, _id:0 } }
]
);


db.ultimiValori.drop();
var ultimiValori = db.createView(
"ultimiValori",
"ultimeModifiche",
[
   { 
      "$lookup": 
      { 
	     from : "tutti",
		 let: {
                variableName: "$VariableName",
                latest: "$latest"
             },
		 pipeline: [
                {
                   $match: {
                      $expr: {
                         $and: [
                            {
                               $eq: [
                                  "$VariableName",
                                  "$$variableName"
                               ]
                            },
                            {
                               $eq: [
                                  "$Date",
                                  "$$latest"
                               ]
                            }
                         ]
                      }
                   }
                }
             ],
		 as: "output"
	   }
   },   
    {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$output", 0 ] }, "$$ROOT" ] } }
   },
   { $project: { output: 0 } }
   
]
);
db.ultimiValori.find();

Get latest value in mongodb nested documents

1 solution

Solution 1

Add your solution here

Preview 0

Get latest value in mongodb nested documents

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us