查询文档-基于作为文档中列表一部分的对象的属性

提问者：小点点

查询文档-基于作为文档中列表一部分的对象的属性

我正在尝试获取符合以下标准的所有文件，但结果似乎不正确。

标准：发布日期与给定范围匹配且此发布时间线处于活动状态的电影

isActive=true并释放日期

每个文档都属于Movie类型，其中包含一个movieTimeline对象列表，每个movieTimeline都有一个isActive和release aseDate属性。在任何时候，单个电影时间线都可以在文档中处于活动状态。

文件的结构-

@Document
public class Movie extends BaseDocument
{
    private String name;
    private List<MovieTimeline> movieTimelines;
...

public class MovieTimeline
{
    private Boolean isActive;
    private LocalDateTime releaseDate;
    private String description;
...

发布日期属性在Java中的类型为LocalDateTime，并以DB的2020-04-20T05:00:00.000 00:00格式存储。

这是我试图检索数据的代码-

@Repository
public interface MovieRepository extends MongoRepository<Movie,String>
{
    List<Movie> findByMovieTimelines_ReleaseDateBetweenAndMovieTimelines_IsActive(LocalDateTime from, LocalDateTime to, boolean isActive);
}

样本数据-

{
"id": "abc",
"name": "Rango",
"movieTimelines": [{
        "isActive": false,
        "releaseDate": "2020-02-10T11:30:00",
        "description": "ORIGINAL TIMELINE"
    }, {
        "isActive": true,
        "releaseDate": "2020-06-15T10:30:00",
        "description": "New Timeline created on 03/10/2020"
    }
]
}, {
    "id": "abcv",
    "name": "Fight Club",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-02-10T11:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": true,
            "releaseDate": "2020-05-18T10:30:00",
            "description": "New Timeline created on 02/05/2020"
        }
    ]
}, {
    "id": "asd",
    "name": "Death Note",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-03-09T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": false,
            "releaseDate": "2020-03-16T10:30:00",
            "description": "New Timeline created on 02/05/2020"
        }, {
            "isActive": false,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }, {
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }
    ]
}, {
    "id": "gfj",
    "name": "Andhadhun",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-04-13T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": true,
            "releaseDate": "2020-07-20T10:30:00",
            "description": "New Timeline created on 03/09/2020"
        }
    ]
}, {
    "id": "nojh",
    "name": "Evan Almighty",
    "movieTimelines": [{
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }
    ]
}

预期-

{
    "id": "asd",
    "name": "Death Note",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-03-09T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": false,
            "releaseDate": "2020-03-16T10:30:00",
            "description": "New Timeline created on 02/05/2020"
        }, {
            "isActive": false,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }, {
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }
    ]
},{
    "id": "nojh",
    "name": "Evan Almighty",
    "movieTimelines": [{
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }
    ]
}

MongoDB是否有任何过滤器或运算符可用于在单个查询中获取这些文档？

共1个答案

匿名用户

MongoDB提供了$elemMatch投影运算符，应该能够满足您的条件。

特别是，您的文档由一系列文档组成。因此，这里是您可以用来构造此类数据查询的另一个参考。

这是我在使用您的条件和示例数据进行测试时发现的。我的查询是在Mongo Shell中，而不是Java/Spring中，所以请用所需的语言重写它。

由于您只想获取那些具有活动电影时间线的文档，因此“movieTimeline”中的“isActive”属性必须为true。使用以下查询可以仅满足此条件：

db.collectionName.find({ "movieTimelines": { $elemMatch: { "isActive": true } } })

发布日期也需要在给定的范围内，这意味着查询需要解决“movieTimeline”中的“release aseDate”属性。这个条件可以这样查询：

db.collectionName.find({ "movieTimelines": { $elemMatch: { "releaseDate": { $gte: ISODate("2020-04-20T00:00:00"), $lte: ISODate("2020-04-21T00:00:00") } } } })

或

db.collectionName.find({ "movieTimelines": { $elemMatch: { "releaseDate": { $gte: ISODate("2020-04-20"), $lte: ISODate("2020-04-21") } } } })

对于上述查询，“release aseDate”值是“ISODate（）”对象，而不是我数据库中的字符串。可以使用toDate、dateFromString等聚合器将字符串转换为ISODate对象。

即使“release aseDate”值是字符串，如果条件中的数据类型更改为字符串，查询也会起作用。这可能不是一个好的做法。

db.collectionName.find({ "movieTimelines": { $elemMatch: { "releaseDate": { $gte: "2020-04-20", $lte: "2020-04-21" } } } })

因此，为了满足您的两个条件，需要将两个查询组合成一个，如下所示：

db.collectionName.find({ "movieTimelines": { $elemMatch: { "isActive": true, "releaseDate": { $gte: ISODate("2020-04-20T00:00:00"), $lte: ISODate("2020-04-21T00:00:00") } } } })

我希望这回答了你的问题。