Mongodb - Return embeded data acording by conditions-CodePudding

i am new in mongodb and what i found are only exaples for basic case. Unfortunately, neither the manual was not helpful.

I need get response with filtered data from embed document for example:

{
    "_id": {
        "$oid": "615c74d32f86c312a50da3d2"
    },
    "basic_column": "post number 1",
    "basic_column": "lorem ipsum",
    "embededData": [{
        "date": {
            "$date": "2021-11-05T00:00:00.000Z"
        },
        "embeded_column_1": 23
    }, {
        "date": {
            "$date": "2022-11-06T00:00:00.000Z"
        },
        "embeded_column_1": 24
    }]
},{
    "_id": {
        "$oid": "618c74d32f86c312a50da3d2"
    },
    "basic_column": "post number 2",
    "basic_column": "lorem ipsum",
    "embededData": [{
        "date": {
            "$date": "2021-11-05T00:00:00.000Z"
        },
        "embeded_column_1": 25
    }, {
        "date": {
            "$date": "2021-11-06T00:00:00.000Z"
        },
        "embeded_column_1": 26
    }]
}

i need from this get result where is condition for "basic_column" = "lorem ipsum" and have in "embededData" documents only for "date" > 2020 and "date" < 2022

so result should be

    {
        "_id": {
            "$oid": "615c74d32f86c312a50da3d2"
        },
        "basic_column": "post number 1",
        "basic_column": "lorem ipsum",
        "embededData": [{
            "date": {
                "$date": "2021-11-05T00:00:00.000Z"
            },
            "embeded_column_1": 23,
        }]
    },{
        "_id": {
            "$oid": "618c74d32f86c312a50da3d2"
        },
        "basic_column": "post number 2",
        "basic_column": "lorem ipsum",
        "embededData": [{
            "date": {
                "$date": "2021-11-05T00:00:00.000Z"
            },
            "embeded_column_1": 25,
        }, {
            "date": {
                "$date": "2021-11-06T00:00:00.000Z"
            },
            "embeded_column_1": 26
        }]
    }

thank you for help

CodePudding user response：

There's an excellent npm package for making queries on JSON. And also, basic_column appears twice in a dictionary, which is invalid. Each key should be unique in a dictionary.

CodePudding user response：

Query

match to keep only documents that have "basic_column" = "lorem ipsum"
filter and keep only the members with year of date >2020 < 2022
($year operator is used to do this)
if you want to filter out document that have empty embeddedData add this also
{"$match": {"$expr": {"$ne": ["$embededData", []]}}}

Test code here

aggregate(
[{"$match": {"basic_column": {"$eq": "lorem ipsum"}}},
  {"$set": 
    {"embededData": 
      {"$filter": 
        {"input": "$embededData",
          "cond": 
          {"$let": 
            {"vars": {"date_year": {"$year": "$$this.date"}},
              "in": 
              {"$and": 
                [{"$gt": ["$$date_year", 2020]},
                  {"$lt": ["$$date_year", 2022]}]}}}}}}}])