mongodb query different array have different condition-CodePudding

I have the following datas.

[{
    "_id" : ObjectId("abc123"),
    "device_id": "A001",
    "A_status": "VALID",
    "B_status": "VALID"
},
{
    "_id" : ObjectId("abc223"),
    "device_id": "A003",
    "A_status": "EXPIRED",
    "B_status": "VALID"
},
{
    "_id" : ObjectId("abc323"),
    "device_id": "B001",
    "A_status": "EXPIRED",
    "B_status": "VALID"
},
{
    "_id" : ObjectId("abc423"),
    "device_id": "B002",
    "A_status": "VALID",
    "B_status": "EXPIRED"
},]

I have two different device_id list:
a_list = ["A001", "A003", ...]
b_list = ["B001", "B002", ...]

a_list need match A_status is VALID, b_list need match A_status is VALID.

I want to find deive_id in a_list and A_status is VALID, deive_id in b_list and B_status is VALID,
so I will get following correct data

{
    "_id" : ObjectId("abc123"),
    "device_id": "A001",
    "A_status": "VALID",
    "B_status": "VALID"
},
{
    "_id" : ObjectId("abc323"),
    "device_id": "B001",
    "A_status": "EXPIRED",
    "B_status": "VALID"
}

How do I execute once query and get the answer? Or have to separate queries for different conditions?

CodePudding user response：

You can use Regex to get different data from MongoDB. To get model AXXX and A_status is VALID you can use this query.

{
    device_model: { $regex :/^A/},
    A_status: 'VALID'
}

To get BXXX and B_status is VALID you can use:

{
    device_model: { $regex :/^B/},
    B_status: 'VALID'
}

It may be useful to take a look into mongo regex documentation.

CodePudding user response：

Use $or

db.collection.find({
  $or: [
    {
      A_status: "VALID",
      device_model: {
        $in: [
          "A001",
          "A003"
        ]
      }
    },
    {
      B_status: "VALID",
      device_model: {
        $in: [
          "B001",
          "B002"
        ]
      }
    }
  ]
})

mongoplayground