Need Help With Aggregation Pipeline

Anja_H · January 24, 2023, 10:13pm

Hello, I am beginner with MongoDB. I need help with the following pipeline:

db.getCollection('songs').aggregate([
  {
    $match: {
      correct:true, 
      createdAt : { '$gte' : ISODate('2022-02-01'), '$lte' :ISODate('2022-02-09') },

      covers: {
          $elemMatch: {
            "relation.correct": false
        }
      }
    }
  },
  {
    $project: {
            
      covers: {
        $filter: {
                        
          input: "$covers",
          as: "cover",
          cond: { $eq: ["$$cover.relation.correct", false],
          }
        }
      }
    }
  },
       
    {
    $lookup: {
        from: "songs",
        let: { covers_song: "$covers.song" },
        pipeline: [
            {
                $match: {
                    $expr: {
                        $and: [
                            { $eq: ["$_id", "$$covers_song"] },
                            { $eq: ["$correct", true] }
                        ]
                    }
                }
            }
        ],
        as: "cover_songs"
    }
}

    
])

The result of cover_songs is null elements and I don’t know why? Who can help me?

steevej · January 24, 2023, 11:20pm

It would really help to help you if you share some sample documents from the collection. You might have a typo in some of the field names.

Anja_H · January 25, 2023, 1:06pm

Here a sample document: It is a database with original-songs and related covers:

{
    "_id" : ObjectId("5acc307fd734b019b873e90c"),
    "title" : "Listen To Your Heart",
    "artists" : [ 
        {
            "artist" : ObjectId("5acc307ad734b019b871cac8"),
            "name_id" : "jft428eo3qepqgvrtvw",
            "delimiter" : null
        }
    ],
    "composers" : [ 
        {
            "artist" : ObjectId("5acc307ed734b019b873d978")
        }, 
        {
            "artist" : ObjectId("5acc307ad734b019b8728a0e")
        }
    ],
    "search_terms" : [ 
        {
            "term" : "listentoyourheart"
        }
    ],
    "legacy_id" : 16679,
    "legacy_artists" : "Roxette",
    "legacy_composers" : "Per Håkan Gessle / Mats Persson",
    "sources" : [ 
        {
            "source" : "bla bla bla"
        }, 
        
    ],
    "cover_count" : 16,
    "covers" : [ 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9b0")
        }, 
     
    "comment_de" : "",
    "editor" : "MIG",
    "edited_by" : "AHO",
    "note" : "",
    "release_date" : "1988",
    "createdAt" : ISODate("2005-05-13T22:00:00.000Z"),
    "updatedAt" : ISODate("2023-01-13T16:15:17.921Z"),
    "languages" : [ 
        "eng"
    ],
    "correct" : true,
    "youtube" : {
        "modified" : ISODate("2022-12-04T02:04:52.052Z"),
        "autogenerated" : false,
        "id" : "yCC_b5WHLX0"
    },
    "original_count" : 1,
    "url_key" : "Roxette-Listen-To-Your-Heart",
    "first_release" : {
        "medium" : "7''-Vinyl",
        "title" : "Listen To Your Heart",
        "label" : "Parlophone",
        "record_number" : "1363237",
        "country" : "SE",
        "track_number" : "A",
        "ean" : "",
        "asin" : "",
        "medium_description" : "Single"
    },
    "folksong" : {
        "identifiers" : []
    },
    "is_folksong" : false,
    "live_version" : false,
    "highlight" : {
        "is" : false
    },
    "recorded_date" : "",
    "keywords" : [ 
        "roxette", 
        "listen", 
        "2", 
        "yore", 
        "heart", 
        "to", 
        "your"
    ]
}

I want to know: original songs are correct (song completely
processed), and cover songs where correct = song.relation = false

The result of my query:

{
    "_id" : ObjectId("61fe7e1fe5bbd565edbb9868"),
    "covers" : [ 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2001-08-31T22:00:00.000Z"),
                "updatedAt" : ISODate("2001-08-31T22:00:00.000Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc309bd734b019b8793d3c")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2001-08-31T22:00:00.000Z"),
                "updatedAt" : ISODate("2001-08-31T22:00:00.000Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
    "cover_songs" : []
}

Why “cover_songs”: is 0?

steevej · January 26, 2023, 3:42am

When I try to cut-n-paste your document into my mongosh I get:

SyntaxError: Unexpected token, expected "," (53:17)

  51 |         }, 
  52 |      
> 53 |     "comment_de" : "",
     |                  ^
  54 |     "editor" : "MIG",
  55 |     "edited_by" : "AHO",
  56 |     "note" : "",

It looks like the covers array, which is central to your use-case, is not correct.

It will also be nice that the sample documents you supply are the one you used in your sample result. You sample document has _id:5acc307fd734b019b873e90c but your result has _id:61fe7e1fe5bbd565edbb9868. If we do not see the source document of 61fe7e1fe5bbd565edbb9868 there is not way for us to see if the issue is with the document or with the aggregation.

Anja_H · January 26, 2023, 12:37pm

Hello, here the correct sample for _id:5acc307fd734b019b873e90c
{

   "_id" : ObjectId("5acc307fd734b019b873e90c"),
    "title" : "Listen To Your Heart",
    "artists" : [ 
        {
            "artist" : ObjectId("5acc307ad734b019b871cac8"),
            "name_id" : "jft428eo3qepqgvrtvw",
            "delimiter" : null
        }
    ],
    "composers" : [ 
        {
            "artist" : ObjectId("5acc307ed734b019b873d978")
        }, 
        {
            "artist" : ObjectId("5acc307ad734b019b8728a0e")
        }
    ],
    "search_terms" : [ 
        {
            "term" : "listentoyourheart"
        }
    ],
    "legacy_id" : 16679,
    "legacy_artists" : "Roxette",
    "legacy_composers" : "Per Håkan Gessle / Mats Persson",
    "sources" : [ 
        {
            "source" : "https://www.discogs.com/Roxette-Listen-To-Your-Heart/release/1404673"
        }, 
        {
            "source" : "https://en.wikipedia.org/wiki/Listen_to_Your_Heart_(Roxette_song)"
        }, 
        {
            "source" : "https://de.wikipedia.org/wiki/Listen_to_Your_Heart"
        }
    ],
    "cover_count" : 16,
    "covers" : [ 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9b0")
        }, 
        {
            "relation" : {
                "relation_type" : "A",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2022-03-06T22:38:31.640Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5ae38d4ec8b438ca3f4a4e51")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2022-03-18T21:12:46.044Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9aa")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9b2")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9af")
        }, 
        {
            "relation" : {
                "relation_type" : "Q",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2023-01-13T16:55:41.785Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9b1")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ae")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2021-11-10T07:34:55.744Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9a9")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-04-20T14:35:05.514Z"),
                "updatedAt" : ISODate("2022-04-20T14:35:22.944Z")
            },
            "song" : ObjectId("626019f251685b0de4874798")
        }, 
        {
            "relation" : {
                "relation_type" : "M",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "00:23",
                        "cover" : "01:22"
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-03-20T10:55:47.538Z"),
                "updatedAt" : ISODate("2022-03-20T10:56:18.275Z")
            },
            "song" : ObjectId("6236ff8405189150766ebcdc")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ad")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ac")
        }, 
        {
            "relation" : {
                "relation_type" : "Q",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ab")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9a8")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2021-11-09T22:31:55.385Z"),
                "updatedAt" : ISODate("2021-11-09T22:31:55.385Z")
            },
            "song" : ObjectId("618af6c3a151e6db261f93a8")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-08-30T05:41:01.337Z"),
                "updatedAt" : ISODate("2022-08-30T05:42:41.476Z")
            },
            "song" : ObjectId("630da214ee9bd995ee18bba5")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2023-01-06T18:08:37.450Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9a7")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-08-17T12:18:27.292Z"),
                "updatedAt" : ISODate("2022-08-17T12:19:30.931Z")
            },
            "song" : ObjectId("62fcdc699336888e0e569e25")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-07-31T07:01:47.859Z"),
                "updatedAt" : ISODate("2022-07-31T07:01:47.859Z")
            },
            "song" : ObjectId("62e6285dd1aee7826833d486")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-08-30T05:25:01.705Z"),
                "updatedAt" : ISODate("2022-08-30T05:25:01.705Z")
            },
            "song" : ObjectId("630d9e1cee9bd995ee184386")
        }, 
        {
            "relation" : {
                "relation_type" : "Q",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9a6")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-08-30T09:20:57.658Z"),
                "updatedAt" : ISODate("2022-08-30T09:20:57.658Z")
            },
            "song" : ObjectId("630dd5c5ee9bd995ee1e967e")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-08-30T09:27:46.987Z"),
                "updatedAt" : ISODate("2022-08-30T09:27:46.987Z")
            },
            "song" : ObjectId("630dd784ee9bd995ee1ecb91")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2020-08-03T19:34:16.244Z"),
                "updatedAt" : ISODate("2020-08-03T19:37:31.340Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5f286613520cbf88170d8d7d")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-08-30T09:38:54.379Z"),
                "updatedAt" : ISODate("2022-08-30T09:38:54.379Z")
            },
            "song" : ObjectId("630dd93eee9bd995ee1efed0")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-02-15T20:23:56.733Z"),
                "updatedAt" : ISODate("2022-02-15T20:23:56.733Z")
            },
            "song" : ObjectId("620c0a59b0343c2ea9456d3d")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2021-09-13T05:55:42.481Z"),
                "updatedAt" : ISODate("2021-10-09T05:28:12.389Z")
            },
            "song" : ObjectId("613ee736d6db78d96a0b160c")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : true,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "comment_de" : "",
                "comment_en" : "",
                "createdAt" : ISODate("2022-08-30T06:14:05.816Z"),
                "updatedAt" : ISODate("2022-08-30T06:14:05.816Z")
            },
            "song" : ObjectId("630daa0fee9bd995ee19a76f")
        }
    ],
    "comment_de" : "",
    "editor" : "MIG",
    "edited_by" : "AHO",
    "note" : "",
    "release_date" : "1988",
    "createdAt" : ISODate("2005-05-13T22:00:00.000Z"),
    "updatedAt" : ISODate("2023-01-13T16:15:17.921Z"),
    "languages" : [ 
        "eng"
    ],
    "correct" : true,
    "youtube" : {
        "modified" : ISODate("2022-12-04T02:04:52.052Z"),
        "autogenerated" : false,
        "id" : "yCC_b5WHLX0"
    },
    "original_count" : 1,
    "url_key" : "Roxette-Listen-To-Your-Heart",
    "first_release" : {
        "medium" : "7''-Vinyl",
        "title" : "Listen To Your Heart",
        "label" : "Parlophone",
        "record_number" : "1363237",
        "country" : "SE",
        "track_number" : "A",
        "ean" : "",
        "asin" : "",
        "medium_description" : "Single"
    },
    "folksong" : {
        "identifiers" : []
    },
    "is_folksong" : false,
    "live_version" : false,
    "highlight" : {
        "is" : false
    },
    "recorded_date" : "",
    "keywords" : [ 
        "roxette", 
        "listen", 
        "2", 
        "yore", 
        "heart", 
        "to", 
        "your"
    ]
}

I changed the skript:
db.getCollection('songs').aggregate([
  {
    $match: {
      correct:true, title: "Listen To Your Heart",
      createdAt : { '$gte' : ISODate('1999-02-01'), '$lte' :ISODate('2022-02-09') },

      covers: {
          $elemMatch: {
            "relation.correct": false
        }
      }
    }
  },
  {
    $project: {
            
      covers: {
        $filter: {
                        
          input: "$covers",
          as: "cover",
          cond: { $eq: ["$$cover.relation.correct", false],
          }
        }
      }
    }
  },
       
    {
    $lookup: {
        from: "songs",
        let: { covers_song: "$covers.song" },
        pipeline: [
            {
                $match: {
                    $expr: {
                        $and: [
                            { $eq: ["$_id", "$$covers_song"] },
                            { $eq: ["$correct", true] }
                        ]
                    }
                }
            }
        ],
        as: "cover_songs"
    }
}

the result of my query:

{
    "_id" : ObjectId("5acc307fd734b019b873e90c"),
    "covers" : [ 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9b0")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9b2")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9af")
        }, 
        {
            "relation" : {
                "relation_type" : "Q",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2023-01-13T16:55:41.785Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9b1")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ae")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ad")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ac")
        }, 
        {
            "relation" : {
                "relation_type" : "Q",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9ab")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.336Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9a8")
        }, 
        {
            "relation" : {
                "relation_type" : "C",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2023-01-06T18:08:37.450Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9a7")
        }, 
        {
            "relation" : {
                "relation_type" : "Q",
                "correct" : false,
                "timestamps" : [ 
                    {
                        "original" : "",
                        "cover" : ""
                    }
                ],
                "createdAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "updatedAt" : ISODate("2019-06-25T19:36:47.337Z"),
                "comment_de" : "",
                "comment_en" : ""
            },
            "song" : ObjectId("5acc308fd734b019b876b9a6")
        }
    ],
    "cover_songs" : []

steevej · January 26, 2023, 6:05pm

The issue is the use of let:.

The let: variable is evaluated once for the top document, not for each element of that needs to be $lookup-ed up. So its value is the array $covers.song so the $match will always evaluate

to false.

A small change to your lookup should do what you want. Try:

{ $lookup : {
    from : "songs" ,
    localField : "covers.song" ,
    foreignField : "_id",
    pipeline : [
         { $match : { correct : true }  }
    ] ,
    as : "cover_songs"
} }

Anja_H · January 26, 2023, 6:35pm

thank you. I got a failure message? “$lookup with ‘pipeline’ may not specify ‘localField’ or ‘foreignField’”, My MongoDB Version is 4.46.

steevej · January 26, 2023, 7:58pm

Bummer!

Consize syntax in new in 5.0.

One way out is to leave out the pipeline: in the $lookup and then do a $project after with a $filter like you did in your existing $project.

Anja_H · January 26, 2023, 9:00pm

I’ve done it in that way:


db.getCollection('songs').aggregate([



  {



    $match: {



      correct:true,



      createdAt : { '$gte' : ISODate('1999-01-01'), '$lte' :ISODate('2000-12-31') },





      covers: {



          $elemMatch: {



            "relation.correct": false



        }



      }



    }



  },

  

  



  {



    $project: {"url_key" : 1,



            



      covers: {



        $filter: {



           input: "$covers",



          as: "cover",



          cond: { $eq: ["$$cover.relation.correct", false],



          }



        }



      }



    }



  },



{

    $lookup: {

      from: "songs",

      localField: "covers.song",

      foreignField: "_id",
        
      

      as: "coverversionen",

    }

  },

  {
      $project: {
        "coverversionen._id": 1,
        "coverversionen.url_key": 1,    
         



      coverstrue: {



        $filter: {



           input: "$coverversionen.correct",



          as: "correct",



          cond: { $eq: ["$$correct", true],



          }



        }



      }



    }



  },

 

])

Is it ok in this way? I still need to see the coverstrue.id und coverstrue.url_key fields?
Can you help?

steevej · January 27, 2023, 2:31pm

Code with so many blank lines is not readable.

If you get the result you want it is okay. If you do not get the result you want it is not okay? But I really do not know if you get the result you want.

Please stop changing fields names. You had cover_songs and not it seems it is conversionen. It is really hard to follow a moving target.

The sentence ends with a question mark but looks like an affirmation. I really do not know what you need to see.

Anja_H · January 27, 2023, 3:18pm

Hello, thanks at first for your patience. Here is the script without changed field names and without changed blank lines:

db.getCollection('songs').aggregate([
  {
    $match: {
      correct:true,
      createdAt : { '$gte' : ISODate('1999-01-01'), '$lte' :ISODate('2000-12-31') },
      covers: {
          $elemMatch: {
            "relation.correct": false
        }
      }
    }
  },
  {
    $project: {"url_key" : 1,
      covers: {
        $filter: {
           input: "$covers",
          as: "cover",
          cond: { $eq: ["$$cover.relation.correct", false],
          }
        }
      }
    }
  },
  
{    
    $lookup: {
      from: "songs",
      localField: "covers.song",
      foreignField: "_id",
      as : "cover_songs"
    }
  },
   { "$project": {
        "cover_songs.id": 1,
                coverstrue: {
       $filter: {input: "$cover_songs.correct",
          as: "correct",
          cond: { $eq: ["$$correct", true],
          }
      }
  }
}
}
])

Yes, it works in the right way, it filters the songs, where correct = true. My problem now is, that I see only as result: coverstrue.0.true, coverstrue1.true, and so on. I need still the information, what the id for the cover is and the field url_key, (artist of the cover-title and the name of the cover-title?) If this solved, then I am finally ready?

steevej · January 27, 2023, 5:07pm

You only see the fields that you $project.

After the first $project, only the fields url_key and covers the filtered array will be present.

In your final $project, since the input: is $cover_songs.correct you only get the correct field. If you want the other fields of the array elements the input: has to be the whole array. Something like

coverstrue : {
    $filter : {
        input : "$cover_songs" ,
        as : "cover" ,
        cond : { $eq : [ "$$cover.correct" , true ] } ,
    }
}

Anja_H · January 28, 2023, 10:03am

Hey, thank you so much, you are great. It works in the way I want.