Create filter with $and condition, SQL to golang filter converter?

Question

I have currently a working filter for .DeleteMany. It deletes all entries where _id is in the given array vids:

filter := bson.D{{Key: "_id", Value: bson.D{{Key: "$in", Value: vids}}}}
res, err := DB.Collection("data").DeleteMany(context.TODO(), filter)

Now I want to enhance the filter and add some $and condition to only delete entries where _id is in the given array vids and(!) the value of providerid is 1234.

Sadly I'm stuck on how to do that in go. For me it is extremely hard to read and write such filters. Especially with all that bson.D, bson.M and []bson.D and the many curly brackets etc.

In SQL I would write DELETE FROM data WHERE _id IN( {list} ) AND providerid=1234;

Is there any SQL to golang mongodb filter converter?

Tushar Gupta - curioustushar · Accepted Answer · 2021-03-24 16:27:03Z

2

Try

filter := bson.D{
    { "$and", []interface{}{
        bson.D{{ Key: "_id", Value: bson.D{{ Key: "$in", Value: vids }}}},
        bson.D{{ "providerid", 123}},
    }},
}

As per @VolkerSchmid comment

filter := bson.D{ { Key: "$and", Value: []interface{}{ bson.D{{ Key: "_id", Value: bson.D{{ Key: "$in", Value: vids }}}}, bson.D{{ Key: "providerid", Value: 123}}, }}, }

edited Mar 24, 2021 at 16:27

answered Mar 24, 2021 at 16:03

Tushar Gupta - curioustushar

57.2k24 gold badges106 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Volker Over a year ago

Thanks Tushar, but this triggers "go.mongodb.org/mongo-driver/bson/primitive.E composite literal uses unkeyed fields" in VS Code. To get rid of this, I need to write like this:filter := bson.D{ { Key: "$and", Value: []interface{}{ bson.D{{ Key: "_id", Value: bson.D{{ Key: "$in", Value: vids }}}}, bson.D{{ Key: "providerid", Value: 123}}, }}, } But is there some code generator or such, making it more easy to write such filters?

Volker Over a year ago

Strange that mongodb needs us to write 10 opening curly brackets, 10 closing curly brackets and a set of square brackets just for such a simple filter... Some generator would be of great help.

Volker Over a year ago

yes, it works after adaption with Key: and Value:. Thanks for this! But the main part of my question was about a SQL to mongodb converter.

Tushar Gupta - curioustushar Over a year ago

@VolkerSchmid docs.mongodb.com/manual/reference/sql-comparison studio3t.com/knowledge-base/articles/sql-query site24x7.com/tools/sql-to-mongodb.html

Volker Over a year ago

Gupta Thank you, this is a good start. But it just generates a mongodb search filter. It still lacks all the golang related things like interface, bson.D etc... It also adds some weird {"E": 1} to the query I dont understand. But only if the query is a DELETE query. But helpful anyway.

|

Collectives™ on Stack Overflow

Create filter with $and condition, SQL to golang filter converter?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related