Find documents where a field value is in a dynamic list

Is there any way to apply this SQL query on MongoDB?
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2 WHERE condition1 AND condition2 AND condition3)
One solution is to do this through two queries, one for fetching the list and the other for finding the docs based on the fetched list. The problem with this solution is that there is no guarantee that these two operations will be done in an atomic manner because we are not applying these operations inside a single query. In the other words, what if the fetched list being changed after we fetch it and before we apply the second query?The result may be different if the items in the list being changed.

Please provide some valid JSON documents from both collections (tables) with valid queries for your conditions.

Otherwise we have to create our own documents. Help us help you by providing some data that we can work with.

The general idea would be to $lookup in table2 using the conditions and then to $match documents that gives an empty $lookup array. Not the most efficient, but that’s what you get when using not or not-equal (SQL or not) as both are not selective.

1 Like

Thank you for your attention. In fact, table1 and table2 are the same and equivalent to “order” collection.

Here is the exact query and three JSON documents:

select * from order where ( (order_status == ‘WAITING_PAYMENT’ ) or (order_status == ‘CLOSE’ and id not in (select base_order_id from order where order_status in (‘CLOSE’,‘CHECK_DOCUMENT’,‘WAITING_PAYMENT’) and base_order_id is not null)))


{
	"_id" : NumberLong(1),
	"insure_type" : "MEDICAL",
	"company" : "DAY",
	"base" : true,
	"request_date" : ISODate("2022-01-11T07:44:37.707Z"),
	"new_expire_date" : ISODate("2022-01-21T20:30:00.000Z"),
	"order_status" : "CLOSE",
	"device_type" : "WEB_DESKTOP",
	"customer_id" : NumberLong(959345),
	"old_company_id" : NumberLong(0),
	"price" : NumberLong(26160),
	"discounted_price" : NumberLong(26016),
	"duration" : "ONE_YEAR"
}
{
	"_id" : NumberLong(2),
	"insure_type" : "MEDICAL",
	"company" : "DAY",
	"base" : false,
	"request_date" : ISODate("2022-01-11T07:44:37.707Z"),
	"new_expire_date" : ISODate("2022-01-21T20:30:00.000Z"),
	"order_status" : "CLOSE",
	"device_type" : "WEB_DESKTOP",
	"customer_id" : NumberLong(959345),
	"old_company_id" : NumberLong(0),
	"price" : NumberLong(26160),
	"discounted_price" : NumberLong(26016),
	"duration" : "ONE_YEAR"
}
{
	"_id" : NumberLong(3),
	"insure_type" : "MEDICAL",
	"company" : "DAY",
	"base" : false,
	"request_date" : ISODate("2022-01-25T07:08:41.703Z"),
	"order_status" : "WAITING_PAYMENT",
	"device_type" : "WEB_DESKTOP",
	"old_expire_date" : ISODate("2022-01-21T20:30:00.000Z"),
	"customer_id" : NumberLong(959345),
	"old_company_id" : NumberLong(0),
	"price" : NumberLong(32700),
	"discounted_price" : NumberLong(32556),
	"duration" : "ONE_YEAR",
	"base_order_id" : 1
}

The result should be the documents with id=2 and id=3 because the document with id=1 matches with the exclusion list conditions.

Please read Formatting code and log snippets in posts and update your documents so that we can cut-n-paste them directly into our system.

Actually, I found the solution after some research. Here is the query which solved the problem:
db.order.aggregate( [
{$lookup:
{
from: “order”,
localField: “_id”,
foreignField: “base_order_id”,
pipeline: [
{ $match:
{ $and:
[
{“order_status” :{ $in : [‘CLOSE’,‘CHECK_DOCUMENT’,‘WAITING_PAYMENT’]} },
{“base_order_id” : {$ne :null}}
]
}
},
{$project : {_id:1}}
],
as: “exclusion”
}
},
{$match : {$or :[ {“order_status” : ‘WAITING_PAYMENT’},
{$and: [{“order_status” : ‘CLOSE’,“exclusion”: {$size:0}}]}]}}
] )

1 Like