mongodb查询json某个节点 mongodb objectid查询

转载

charlesc 2023-08-24 12:48:47

文章标签 mongodb查询json某个节点 mongodb模糊查询 update 两个条件 mysql mongodb 文章分类 MongoDB 数据库

mongodb查询json某个节点 mongodb objectid查询_mongodb查询json某个节点

上一篇Django 2.1.7 模型 - 条件查询、模糊查询、空查询、比较查询、范围查询、日期查询讲述了关于Django模型的查询。

但是都是条件与常量的查询，以及单条件查询，那么本篇章来介绍F对象、Q对象、聚合查询等功能。

参考文献

https://docs.djangoproject.com/zh-hans/2.1/topics/db/queries/

F对象

之前的查询都是对象的属性与常量值比较，两个属性怎么比较呢？先来看看已有的mysql数据，如下：

mysql> select * from assetinfo_middlewareinfo;
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| id | name      | port  | server_id | is_delete | shelves_date               | update_time                |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
|  1 | memcached | 11211 |         1 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 |
|  2 | redis     |  6379 |         1 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |
|  3 | nginx     |    80 |         2 |         1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
|  4 | kafka     |  9092 |         2 |         1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
|  5 | test      |   123 |         1 |         1 | 2019-06-10 17:05:16.632773 | 2019-06-10 17:05:16.632773 |
|  6 | test      |   123 |         1 |         1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 |
|  7 | test      |   123 |         1 |         1 | 2019-06-10 17:06:43.193412 | 2019-06-10 17:06:43.193412 |
|  8 | test      |   123 |         1 |         1 | 2019-06-10 17:07:03.747395 | 2019-06-10 17:07:03.747395 |
|  9 | test      |   123 |         1 |         1 | 2019-06-10 17:08:43.372097 | 2019-06-10 17:08:43.372097 |
| 10 | test      |   123 |         1 |         1 | 2019-06-10 17:09:37.877019 | 2019-06-10 17:09:37.877019 |
| 11 | test      |   123 |         1 |         1 | 2019-06-10 17:11:45.403627 | 2019-06-10 17:11:45.403627 |
| 12 | mysql     |  3306 |         2 |         0 | 2019-06-10 17:12:12.558217 | 2019-06-10 17:12:12.558217 |
| 13 | mongodb   |  3388 |         2 |         1 | 2019-06-10 17:15:18.327729 | 2019-06-10 17:15:18.327729 |
| 14 | mongodb   |  3306 |         1 |         1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
| 15 | test      |   123 |         1 |         0 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 |
| 16 | test      |  3306 |         1 |         0 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
16 rows in set (0.00 sec)

可以看到上面的数据存在 shelves_date 与 update_time 不相等的情况，之前的常量比较SQL如下：

mysql> select * from assetinfo_middlewareinfo where shelves_date > "2019-06-10 17:38:20.712862" ;
+----+---------+------+-----------+-----------+----------------------------+----------------------------+
| id | name    | port | server_id | is_delete | shelves_date               | update_time                |
+----+---------+------+-----------+-----------+----------------------------+----------------------------+
| 14 | mongodb | 3306 |         1 |         1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
| 15 | test    |  123 |         1 |         0 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 |
| 16 | test    | 3306 |         1 |         0 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 |
+----+---------+------+-----------+-----------+----------------------------+----------------------------+
3 rows in set (0.00 sec)

mysql>

那么如果需要使用 shelves_date 与 update_time 进行大小比较，如下：

mysql> select * from assetinfo_middlewareinfo where shelves_date < update_time ;
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| id | name      | port  | server_id | is_delete | shelves_date               | update_time                |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
|  1 | memcached | 11211 |         1 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 |
|  2 | redis     |  6379 |         1 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |
|  3 | nginx     |    80 |         2 |         1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
|  4 | kafka     |  9092 |         2 |         1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
|  6 | test      |   123 |         1 |         1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 |
| 14 | mongodb   |  3306 |         1 |         1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
6 rows in set (0.00 sec)

mysql>

那么这种SQL按照上一篇的内容是无法实现的，下面来介绍F对象来解决这个问题。

语法如下：

F(属性名)

使用F对象需要导入库，如下：

from django.db.models import F

下面使用模型来查询 shelves_date < update_time 的结果，如下：

In [4]: from assetinfo.models import ServerInfo,MiddlewareInfo

In [5]: from django.db.models import F

In [6]: MiddlewareInfo.objects.filter( shelves_date__lt = F('update_time') )
Out[6]: 1)>, 2)>, 3)>, eInfo object (4)>, 6)>, 14)>]>
In [7]:

对应执行的SQL如下：

2019-06-12T15:19:37.735397Z	   12 Query	SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE `assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`)  LIMIT 21

可以看到最后的where条件是

`assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`)  LIMIT 21

那么如果对于比较的变量还要乘以2倍，例如：

mysql> select * from assetinfo_middlewareinfo where shelves_date < (update_time*2) ;
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| id | name      | port  | server_id | is_delete | shelves_date               | update_time                |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
|  1 | memcached | 11211 |         1 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 |
|  2 | redis     |  6379 |         1 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |
|  3 | nginx     |    80 |         2 |         1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
|  4 | kafka     |  9092 |         2 |         1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
|  5 | test      |   123 |         1 |         1 | 2019-06-10 17:05:16.632773 | 2019-06-10 17:05:16.632773 |
|  6 | test      |   123 |         1 |         1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 |
|  7 | test      |   123 |         1 |         1 | 2019-06-10 17:06:43.193412 | 2019-06-10 17:06:43.193412 |
|  8 | test      |   123 |         1 |         1 | 2019-06-10 17:07:03.747395 | 2019-06-10 17:07:03.747395 |
|  9 | test      |   123 |         1 |         1 | 2019-06-10 17:08:43.372097 | 2019-06-10 17:08:43.372097 |
| 10 | test      |   123 |         1 |         1 | 2019-06-10 17:09:37.877019 | 2019-06-10 17:09:37.877019 |
| 11 | test      |   123 |         1 |         1 | 2019-06-10 17:11:45.403627 | 2019-06-10 17:11:45.403627 |
| 12 | mysql     |  3306 |         2 |         0 | 2019-06-10 17:12:12.558217 | 2019-06-10 17:12:12.558217 |
| 13 | mongodb   |  3388 |         2 |         1 | 2019-06-10 17:15:18.327729 | 2019-06-10 17:15:18.327729 |
| 14 | mongodb   |  3306 |         1 |         1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
| 15 | test      |   123 |         1 |         0 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 |
| 16 | test      |  3306 |         1 |         0 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
16 rows in set (0.00 sec)

使用模型的F对象也是可以直接乘以 2 倍处理的，如下：

In [8]: MiddlewareInfo.objects.filter( shelves_date__lt = F('update_time') * 2 )
Out[8]: 1)>, 2)>, 3)>, eInfo object (4)>, 5)>, 6)>, 7)>, eInfo object (8)>, 9)>, 10)>, 11)>, areInfo object (12)>, 13)>, 14)>, 15)>, dlewareInfo object (16)>]>

对应的SQL语句如下：

2019-06-12T15:26:57.158671Z	   12 Query	SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE `assetinfo_middlewareinfo`.`shelves_date` < ((`assetinfo_middlewareinfo`.`update_time` * 2))  LIMIT 21

可以看到where条件是

`assetinfo_middlewareinfo`.`shelves_date` < ((`assetinfo_middlewareinfo`.`update_time` * 2))  LIMIT 21

Q对象

前面的查询可以看到都是单条件查询，并没有多个条件查询。例如：执行mysql示例如下：

mysql> select * from assetinfo_middlewareinfo where server_id = 2 and shelves_date < update_time ;
+----+-------+------+-----------+-----------+----------------------------+----------------------------+
| id | name  | port | server_id | is_delete | shelves_date               | update_time                |
+----+-------+------+-----------+-----------+----------------------------+----------------------------+
|  3 | nginx |   80 |         2 |         1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
|  4 | kafka | 9092 |         2 |         1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
+----+-------+------+-----------+-----------+----------------------------+----------------------------+
2 rows in set (0.00 sec)

mysql>

可以从上面的where条件看到有两个过滤条件。第一个则是 server_id = 2 ，第二个则是 shelves_date < update_time 那么再模型中，怎么写出来呢？

可以使用Q对象来实现，用法如下：

from django.db.models import Q
Q(属性名__运算符=值) & Q(属性名__运算符=值)  ==> and
Q(属性名__运算符=值) | Q(属性名__运算符=值)   ==> or
~Q(属性名__运算符=值)                       ==> not

使用模型编写该示例，如下：

In [1]: from assetinfo.models import ServerInfo,MiddlewareInfo

In [2]: from django.db.models import F,Q

In [3]: 

In [5]: MiddlewareInfo.objects.filter( Q( server_id__exact = 2 ) & Q( shelves_date__lt = F('update_time')  ) )
Out[5]: 3)>, 4)>]>
In [6]:

对应sql如下：

2019-06-13T15:43:47.042246Z	   14 Query	SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE (`assetinfo_middlewareinfo`.`server_id` = 2 AND `assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`))  LIMIT 21

上面是and条件的示例，那么现在来执行一下 or ，如下：

In [6]: MiddlewareInfo.objects.filter( Q( server_id__exact = 2 ) | Q( shelves_date__lt = F('update_time')  ) )
Out[6]: 1)>, 2)>, iddlewareInfo object (3)>, 4)>, 6)>, eInfo: MiddlewareInfo object (12)>, 13)>, 14)>]
>

对应的SQL如下：

2019-06-13T15:47:09.063544Z	   14 Query	SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE (`assetinfo_middlewareinfo`.`server_id` = 2 OR `assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`))  LIMIT 21

最后再写一个not关系的，如下：

In [7]: MiddlewareInfo.objects.filter( ~Q( server_id__exact = 2 ) )
Out[7]: 1)>, 2)>, iddlewareInfo object (5)>, 6)>, 7)>, eInfo: MiddlewareInfo object (8)>, 9)>, 10)>, <
MiddlewareInfo: MiddlewareInfo object (11)>, 14)>, ct (15)>, 16)>]>

对应的执行SQL如下：

mysql> SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE NOT (`assetinfo_middlewareinfo`.`server_id` = 2)  LIMIT 21;
+----+-----------+-------+-----------+----------------------------+----------------------------+-----------+
| id | name      | port  | server_id | shelves_date               | update_time                | is_delete |
+----+-----------+-------+-----------+----------------------------+----------------------------+-----------+
|  1 | memcached | 11211 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 |         1 |
|  2 | redis     |  6379 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |         1 |
|  5 | test      |   123 |         1 | 2019-06-10 17:05:16.632773 | 2019-06-10 17:05:16.632773 |         1 |
|  6 | test      |   123 |         1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 |         1 |
|  7 | test      |   123 |         1 | 2019-06-10 17:06:43.193412 | 2019-06-10 17:06:43.193412 |         1 |
|  8 | test      |   123 |         1 | 2019-06-10 17:07:03.747395 | 2019-06-10 17:07:03.747395 |         1 |
|  9 | test      |   123 |         1 | 2019-06-10 17:08:43.372097 | 2019-06-10 17:08:43.372097 |         1 |
| 10 | test      |   123 |         1 | 2019-06-10 17:09:37.877019 | 2019-06-10 17:09:37.877019 |         1 |
| 11 | test      |   123 |         1 | 2019-06-10 17:11:45.403627 | 2019-06-10 17:11:45.403627 |         1 |
| 14 | mongodb   |  3306 |         1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |         1 |
| 15 | test      |   123 |         1 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 |         0 |
| 16 | test      |  3306 |         1 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 |         0 |
+----+-----------+-------+-----------+----------------------------+----------------------------+-----------+
12 rows in set (0.00 sec)

聚合函数

使用aggregate()过滤器调用聚合函数。聚合函数包括：Avg，Count，Max，Min，Sum，被定义在django.db.models中。

首先执行一个sql的聚合函数，如下：

mysql> select count(1) from assetinfo_middlewareinfo;
+----------+
| count(1) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

mysql>

在日常的业务中，经常有统计表数量的情况，那么模型需要怎么写呢？如下：

In [9]: MiddlewareInfo.objects.count()
Out[9]: 16

那么sum方法呢？如下：

mysql> select sum(server_id) from assetinfo_middlewareinfo;
+----------------+
| sum(server_id) |
+----------------+
|             20 |
+----------------+
1 row in set (0.00 sec)

对应模型如下：

In [14]: from django.db.models import Sum

In [15]: MiddlewareInfo.objects.aggregate(Sum('server_id'))
Out[15]: {'server_id__sum': Decimal('20')}

In [16]:

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。