go语言实现mongodb数组过滤
前言
mongodb查询中实现数组过滤有两种方法，分别是：
- 聚合查询 使用
$unwind将数组打散,获取结果集后用$match筛选符合条件的数据，最后使用$group进行聚合获取最终结果集 - 普通查询 先筛选记录，然后通过投影查询过滤数组
第二种方法简单易于操作，所以优先选择第二种方法。
mongo查询
数据：
{
"_id": "0069c99ca882f0f20a77be6230ff26f5951c7789",
"asset_id": "0069c99ca882f0f20a77be6230ff26f5951c7789",
"metadata": {
"timestamp": {
"start": 1727054366,
"duration": 0
},
"tvt_type": 0,
"asset_type": 1,
"width": 1280,
"height": 720,
"image_channels": 3,
"byte_size": 271592,
"origin_filename": "南城学府项目作业面_20220502050000_20220502190000_7_86450.jpg"
},
"class_ids": [
3,
1
],
"gt_class_ids": [
1,
3
],
"pred_class_ids": [
3
],
"pred": [
{
"index": 0,
"box": {
"x": 0,
"y": 89,
"w": 915,
"h": 631,
"rotate_angle": 0
},
"class_id": 3,
"score": 0.943848,
"anno_quality": 0,
"tags": {
},
"cm": 1,
"det_link_id": 2,
"class_name": "渣土车",
"polygon": [
],
"mask": "",
"is_crowd": 0,
"type": 0,
"mask_area": 0
},
{
"index": 1,
"box": {
"x": 905,
"y": 72,
"w": 79,
"h": 76,
"rotate_angle": 0
},
"class_id": 3,
"score": 0.916992,
"anno_quality": 0,
"tags": {
},
"cm": 1,
"det_link_id": 0,
"class_name": "渣土车",
"polygon": [
],
"mask": "",
"is_crowd": 0,
"type": 0,
"mask_area": 0
}
],
"cks": {
},
"quality": -1
}
查询语句：
db.getCollection("t000000100000176d0991727677623@t000000100000176d0991727677623").find(
{
$and: [
{ "class_ids": { $in: [3] } },
{ "pred": { $elemMatch: { "class_id": 3, "score": { $gt: 0.5 }, "cm": 2 } } },
]
},
{
"pred": {
$filter: {
input: "$pred",
as: "p",
cond: {
$and: [
{ $eq: ["$$p.class_id", 3] },
{ $gt: ["$$p.score", 0.5] },
{ $eq: ["$$p.cm", 2] }
]
}
}
},
"class_ids": 1,
"metadata": 1,
"gt": 1,
"asset_id": 1,
"gt_class_ids": 1,
"pred_class_ids": 1,
"cks": 1,
"quality": 1
}
)
查询结果：
{ _id: '0b8f88717e05ffbca3faef7b2ff3e94ddfd726a7',
asset_id: '0b8f88717e05ffbca3faef7b2ff3e94ddfd726a7',
metadata:
{ timestamp: { start: 1727596672, duration: 0 },
tvt_type: 0,
asset_type: 1,
width: 500,
height: 374,
image_channels: 3,
byte_size: 36315,
origin_filename: '0b8f88717e05ffbca3faef7b2ff3e94ddfd726a7.jpg' },
class_ids: [ 3 ],
gt_class_ids: [ 3 ],
pred_class_ids: [ 3 ],
gt:
[ { index: 0,
box: { x: 156, y: 1, w: 345, h: 347, rotate_angle: 0 },
class_id: 3,
score: -1,
anno_quality: -1,
tags: {},
cm: 11,
det_link_id: 0,
class_name: '水泥罐车',
polygon: [],
mask: '',
is_crowd: 0,
type: 0,
mask_area: 0 } ],
cks: {},
quality: -1,
pred:
[ { index: 1,
box: { x: 37, y: 184, w: 110, h: 81, rotate_angle: 0 },
class_id: 3,
score: 0.634277,
anno_quality: 0,
tags: {},
cm: 2,
det_link_id: -1,
class_name: '水泥罐车',
polygon: [],
mask: '',
is_crowd: 0,
type: 0,
mask_area: 0 } ] }
{ _id: '0f2d84c53627d922b58fb5bab0daed9b2288be59',
asset_id: '0f2d84c53627d922b58fb5bab0daed9b2288be59',
metadata:
{ timestamp: { start: 1727596672, duration: 0 },
tvt_type: 0,
asset_type: 1,
width: 751,
height: 563,
image_channels: 3,
byte_size: 119941,
origin_filename: '0f2d84c53627d922b58fb5bab0daed9b2288be59.jpg' },
class_ids: [ 3, 0 ],
gt_class_ids: [ 0, 3 ],
pred_class_ids: [ 3 ],
gt:
[ { index: 0,
box: { x: 672, y: 213, w: 79, h: 76, rotate_angle: 0 },
class_id: 3,
score: -1,
anno_quality: -1,
tags: {},
cm: 11,
det_link_id: 1,
class_name: '水泥罐车',
polygon: [],
mask: '',
is_crowd: 0,
type: 0,
mask_area: 0 },
{ index: 1,
box: { x: 61, y: 99, w: 556, h: 387, rotate_angle: 0 },
class_id: 3,
score: -1,
anno_quality: -1,
tags: {},
cm: 11,
det_link_id: 0,
class_name: '水泥罐车',
polygon: [],
mask: '',
is_crowd: 0,
type: 0,
mask_area: 0 },
{ index: 2,
box: { x: 1, y: 111, w: 204, h: 287, rotate_angle: 0 },
class_id: 0,
score: -1,
anno_quality: -1,
tags: {},
cm: 3,
det_link_id: -1,
class_name: '忽略',
polygon: [],
mask: '',
is_crowd: 0,
type: 0,
mask_area: 0 } ],
cks: {},
quality: -1,
pred:
[ { index: 2,
box: { x: 619, y: 240, w: 72, h: 60, rotate_angle: 0 },
class_id: 3,
score: 0.718262,
anno_quality: 0,
tags: {},
cm: 2,
det_link_id: -1,
class_name: '水泥罐车',
polygon: [],
mask: '',
is_crowd: 0,
type: 0,
mask_area: 0 } ] }
go语言实现的mongo查询
项目中使用go语言查询mongo数据库，所以需要将查询语句使用go语言拼接完成。
package main
import (
"context"
"fmt"
"log"
"go.mongodb.org/mongo-driver/bson"
"go.mongodb.org/mongo-driver/mongo"
"go.mongodb.org/mongo-driver/mongo/options"
)
func main() {
// Set client options
clientOptions := options.Client().ApplyURI("mongodb://mysql_initial_user:mysql_initial_passwd@192.168.23.101:27017")
// Connect to MongoDB
client, err := mongo.Connect(context.TODO(), clientOptions)
if err != nil {
log.Fatal(err)
}
// Check the connection
err = client.Ping(context.TODO(), nil)
if err != nil {
log.Fatal(err)
}
fmt.Println("Connected to MongoDB!")
// 指定数据库和集合
collection := client.Database("YMIR-data").Collection("t000000100000176d0991727677623@t000000100000176d0991727677623")
// 查询条件
filter := bson.D{
{"$and", []bson.D{
{{"class_ids", bson.D{{"$in", []interface{}{3}}}}},
{{"pred", bson.D{{"$elemMatch", bson.D{
{"class_id", 3},
{"score", bson.D{{"$gt", 0.3}}},
{"score", bson.D{{"$lt", 0.9}}},
{"cm", 2},
}}}}},
}},
}
fmt.Println("-------------filter---------------")
fmt.Println(filter)
// 投影条件
projection := bson.D{
{"pred", bson.D{
{"$filter", bson.D{
{"input", "$pred"},
{"as", "p"},
{"cond", bson.D{
{"$and", []bson.D{
{{"$eq", []interface{}{"$$p.class_id", 3}}},
{{"$gt", []interface{}{"$$p.score", 0.3}}},
{{"$lt", []interface{}{"$$p.score", 0.9}}},
{{"$eq", []interface{}{"$$p.cm", 2}}},
}},
}},
}},
}},
{"class_ids", 1},
{"metadata", 1},
{"gt", 1},
{"asset_id", 1},
{"gt_class_ids", 1},
{"pred_class_ids", 1},
{"cks", 1},
{"quality", 1},
}
fmt.Println("---------------projection---------------")
fmt.Println(projection)
// 执行查询
cur, err := collection.Find(context.TODO(), filter, options.Find().SetProjection(projection))
if err != nil {
log.Fatal(err)
}
// 遍历结果
var results []bson.M
if err = cur.All(context.TODO(), &results); err != nil {
log.Fatal(err)
}
// 输出结果
fmt.Println("total: ", len(results))
for _, result := range results {
fmt.Println(result)
}
// 断开连接
err = client.Disconnect(context.TODO())
if err != nil {
log.Fatal(err)
}
fmt.Println("Connection to MongoDB closed.")
}
代码执行过程：
新建代码执行文件目录
mkdir mongo
cd mongo
mod命令初始化包管理环境
mod init 文件名
初始化成功之后当前目录下会新建一个go.mod文件，用于记录安装的模块和包
设置包下载代理
go env -w GO111MODULE=on
go env -w GOPROXY=https://goproxy.io,direct
运行代码
go run mongo_test.go
结果：
Connected to MongoDB!
-------------filter---------------
[{$and [[{class_ids [{$in [3]}]}] [{pred [{$elemMatch [{class_id 3} {score [{$gt 0.3}]} {score [{$lt 0.9}]} {cm 2}]}]}]]}]
---------------projection---------------
[{pred [{$filter [{input $pred} {as p} {cond [{$and [[{$eq [$$p.class_id 3]}] [{$gt [$$p.score 0.3]}] [{$lt [$$p.score 0.9]}] [{$eq [$$p.cm 2]}]]}]}]}]} {class_ids 1} {metadata 1} {gt 1} {asset_id 1} {gt_class_ids 1} {pred_class_ids 1} {cks 1} {quality 1}]
total: 6
map[_id:0b8f88717e05ffbca3faef7b2ff3e94ddfd726a7 asset_id:0b8f88717e05ffbca3faef7b2ff3e94ddfd726a7 cks:map[] class_ids:[3] gt:[map[anno_quality:-1 box:map[h:347 rotate_angle:0 w:345 x:156 y:1] class_id:3 class_name:水泥罐车 cm:11 det_link_id:0 index:0 is_crowd:0 mask: mask_areaolygon:[] score:-1 tags:map[] type:0]] gt_class_ids:[3] metadata:map[asset_type:1 byte_size:36315 height:374 image_channels:3 origin_filename:0b8f88717e05ffbca3faef7b2ff3e94ddfd726a7.jpg timestamp:map[duration:0 start:1727596672] tvt_type:0 width:500] pred:[map[anno_quality:0 box:map[h:81 rotate_angle:0 w:110 x:37 y:184] class_id:3 class_name:水泥罐车 cm:2 det_link_id:-1 index:1 is_crowd:0 mask: mask_area:0 polygon:[] score:0.634277 tags:map[] type:0]] pred_class_ids:[3] quality:-1]
map[_id:0f2d84c53627d922b58fb5bab0daed9b2288be59 asset_id:0f2d84c53627d922b58fb5bab0daed9b2288be59 cks:map[] class_ids:[3 0] gt:[map[anno_quality:-1 box:map[h:76 rotate_angle:0 w:79 x:672 y:213] class_id:3 class_name:水泥罐车 cm:11 det_link_id:1 index:0 is_crowd:0 mask: mask_ar polygon:[] score:-1 tags:map[] type:0] map[anno_quality:-1 box:map[h:387 rotate_angle:0 w:556 x:61 y:99] class_id:3 class_name:水泥罐车 cm:11 det_link_id:0 index:1 is_crowd:0 mask: mask_area:0 polygon:[] score:-1 tags:map[] type:0] map[anno_quality:-1 box:map[h:287 rotate_anglw:204 x:1 y:111] class_id:0 class_name:忽略 cm:3 det_link_id:-1 index:2 is_crowd:0 mask: mask_area:0 polygon:[] score:-1 tags:map[] type:0]] gt_class_ids:[0 3] metadata:map[asset_type:1 byte_size:119941 height:563 image_channels:3 origin_filename:0f2d84c53627d922b58fb5bab0daed9288be59.jpg timestamp:map[duration:0 start:1727596672] tvt_type:0 width:751] pred:[map[anno_quality:0 box:map[h:60 rotate_angle:0 w:72 x:619 y:240] class_id:3 class_name:水泥罐车 cm:2 det_link_id:-1 index:2 is_crowd:0 mask: mask_area:0 polygon:[] score:0.718262 tags:map[] type:0]] pred_class_ids:[3] quality:-1]
map[_id:a56151f2a72ba0cbca9545480f1b6b4b3eb18ddd asset_id:a56151f2a72ba0cbca9545480f1b6b4b3eb18ddd cks:map[] class_ids:[2 3] gt:[] gt_class_ids:[] metadata:map[asset_type:1 byte_size:143700 height:576 image_channels:3 origin_filename:a56151f2a72ba0cbca9545480f1b6b4b3eb18ddd.jpg timestamp:map[duration:0 start:1727596672] tvt_type:0 width:704] pred:[map[anno_quality:0 box:map[h:541 rotate_angle:0 w:627 x:6 y:30] class_id:3 class_name:水泥罐车 cm:2 det_link_id:-1 index:1 is_crowd:0 mask: mask_area:0 polygon:[] score:0.357666 tags:map[] type:0]] pred_clads:[2 3] quality:-1]
map[_id:a9c1102d23ca92102bb57695136e1611460061ca asset_id:a9c1102d23ca92102bb57695136e1611460061ca cks:map[] class_ids:[0 2 3] gt:[map[anno_quality:-1 box:map[h:82 rotate_angle:0 w:200 x:811 y:170] class_id:0 class_name:忽略 cm:3 det_link_id:-1 index:0 is_crowd:0 mask: mask_are0 polygon:[] score:-1 tags:map[] type:0] map[anno_quality:-1 box:map[h:175 rotate_angle:0 w:144 x:1760 y:145] class_id:2 class_name:渣土车 cm:11 det_link_id:0 index:1 is_crowd:0 mask: mask_area:0 polygon:[] score:-1 tags:map[] type:0] map[anno_quality:-1 box:map[h:705 rotate_an:0 w:666 x:1255 y:208] class_id:0 class_name:忽略 cm:3 det_link_id:-1 index:2 is_crowd:0 mask: mask_area:0 polygon:[] score:-1 tags:map[] type:0]] gt_class_ids:[0 2] metadata:map[asset_type:1 byte_size:381183 height:1080 image_channels:3 origin_filename:a9c1102d23ca92102bb576956e1611460061ca.jpg timestamp:map[duration:0 start:1727596672] tvt_type:0 width:1920] pred:[map[anno_quality:0 box:map[h:410 rotate_angle:0 w:309 x:945 y:238] class_id:3 class_name:水泥罐车 cm:2 det_link_id:-1 index:2 is_crowd:0 mask: mask_area:0 polygon:[] score:0.605469 tags:m type:0]] pred_class_ids:[2 3] quality:-1]
map[_id:ae4875ffbb45392521606b62cd682ef97bbbee70 asset_id:ae4875ffbb45392521606b62cd682ef97bbbee70 cks:map[] class_ids:[1 3 0] gt:[map[anno_quality:-1 box:map[h:113 rotate_angle:0 w:30 x:962 y:1] class_id:0 class_name:忽略 cm:3 det_link_id:-1 index:0 is_crowd:0 mask: mask_area:polygon:[] score:-1 tags:map[] type:0] map[anno_quality:-1 box:map[h:31 rotate_angle:0 w:55 x:991 y:81] class_id:0 class_name:忽略 cm:3 det_link_id:-1 index:1 is_crowd:0 mask: mask_area:0 polygon:[] score:-1 tags:map[] type:0]] gt_class_ids:[0] metadata:map[asset_type:1 byte_si:486054 height:720 image_channels:3 origin_filename:ae4875ffbb45392521606b62cd682ef97bbbee70.jpg timestamp:map[duration:0 start:1727596672] tvt_type:0 width:1280] pred:[map[anno_quality:0 box:map[h:47 rotate_angle:0 w:36 x:1099 y:75] class_id:3 class_name:水泥罐车 cm:2 det_link-1 index:1 is_crowd:0 mask: mask_area:0 polygon:[] score:0.707031 tags:map[] type:0]] pred_class_ids:[1 3] quality:-1]
map[_id:cc9d0775d5302ebd2d2c2abd89a89c889a42622c asset_id:cc9d0775d5302ebd2d2c2abd89a89c889a42622c cks:map[] class_ids:[2 3 1] gt:[map[anno_quality:-1 box:map[h:74 rotate_angle:0 w:118 x:843 y:172] class_id:2 class_name:渣土车 cm:11 det_link_id:1 index:0 is_crowd:0 mask: mask_a:0 polygon:[] score:-1 tags:map[] type:0] map[anno_quality:-1 box:map[h:246 rotate_angle:0 w:279 x:844 y:208] class_id:2 class_name:渣土车 cm:11 det_link_id:0 index:1 is_crowd:0 mask: mask_area:0 polygon:[] score:-1 tags:map[] type:0] map[anno_quality:-1 box:map[h:228 rotate_an:0 w:245 x:1116 y:169] class_id:3 class_name:水泥罐车 cm:11 det_link_id:2 index:2 is_crowd:0 mask: mask_area:0 polygon:[] score:-1 tags:map[] type:0]] gt_class_ids:[2 3] metadata:map[asset_type:1 byte_size:613432 height:1080 image_channels:3 origin_filename:cc9d0775d5302ebd2d2c89a89c889a42622c.jpg timestamp:map[duration:0 start:1727596672] tvt_type:0 width:1920] pred:[map[anno_quality:0 box:map[h:420 rotate_angle:0 w:528 x:1018 y:346] class_id:3 class_name:水泥罐车 cm:2 det_link_id:-1 index:3 is_crowd:0 mask: mask_area:0 polygon:[] score:0.715332 tags:map[] type:0]] pred_class_ids:[1 2 3] quality:-1]
Connection to MongoDB closed.
