Postgresql JSON对象和数组查询
文章目录
- 一. Postgresql 9.5以下版本
- 1.1 简单查询(缺陷:数组必须指定下标,不推荐)
- 1.1.1 模糊查询
- 1.1.2 等值匹配
- 1.1.3 时间搜索
- 1.1.4 在列表
- 1.1.5 包含
- 1.2 多层级JSONArray(推荐)
- 1.2.1 模糊查询
- 1.2.2 模糊查询 NOT
- 1.2.3 等值匹配
- 1.2.4 等值匹配 NOT
- 1.2.5 时间搜索
- 1.2.6 时间搜索 NOT
- 1.2.7 在列表
- 1.2.8 在列表 NOT
- 1.2.9 包含
- 1.2.10 包含 NOT
- 二. Postgresql 9.5和以上版本
- 2.1 模糊查询
- 2.2 等值匹配
- 2.3 时间搜索
- 2.4 在列表
- 2.5 包含
一. Postgresql 9.5以下版本
1.1 简单查询(缺陷:数组必须指定下标,不推荐)
1.1.1 模糊查询
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' like '%bb%'
address字段是JSONArray类型,所以在路径中,使用数字索引来访问数组元素,从 0 开始计数。

1.1.2 等值匹配
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' = 'bbb'

如果字段是int类型,后面需要添加::int

1.1.3 时间搜索
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,date}' BETWEEN '2023-08-13' AND '2023-08-17'

1.1.4 在列表
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #>> '{0,name}' IN ('bbb','ccc')

1.1.5 包含
SELECT * FROM "public"."tf_low_data_testUser" WHERE "address" #> '{0,roles,0,roleUsers}' @> '["eee"]'
#>:获取在指定路径的 JSON 对象,路径不存在则返回空。返回类型是json(b)#>>:获取在指定路径的 JSON 对象,路径不存在则返回空。返回类型是text

1.2 多层级JSONArray(推荐)
如果表中有一个字段posts,数据结构为
[{"name": "aaa","ports": [{"port": 443,"nickname": "ggg","date": "2023-08-29","address": ["111", "222"]}, {"port": 80,"nickname": "fff","date": "2022-08-29","address": ["333", "444"]}]
}, {"name": "bbb","ports": [{"port": 2443,"nickname": "hhh","date": "2021-08-29","address": ["999"]}, {"port": 280,"nickname": "jjj","date": "2020-08-29","address": ["111111"]}]
}]
1.2.1 模糊查询
查询nickname like '%jj%'
可以看出有两层JSONArray结构
SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (SELECT 1FROM jsonb_array_elements("ports") as arr1(obj1) CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)WHERE (obj2->>'nickname') like '%gg%'
);
当该层级类型是数组就添加
CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)

1.2.2 模糊查询 NOT
SELECT * FROM "public"."tf_low_data_testUser" WHERE NOT EXISTS (SELECT 1FROM jsonb_array_elements("ports") as arr1(obj1) CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)WHERE (obj2->>'nickname') like '%gg%'
);
查的是另外三条数据源

1.2.3 等值匹配
SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (SELECT 1FROM jsonb_array_elements("ports") as arr1(obj1) CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)WHERE (obj2->>'port')::int = 80
);
如果是数字类型后面需要转换
::int,因为->>操作符的返回类型是text

1.2.4 等值匹配 NOT
SELECT * FROM "public"."tf_low_data_testUser" WHERE NOT EXISTS (SELECT 1FROM jsonb_array_elements("ports") as arr1(obj1) CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)WHERE (obj2->>'port')::int = 80
);
查的是另外三条数据源

1.2.5 时间搜索
SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (SELECT 1FROM jsonb_array_elements("ports") as arr1(obj1) CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)WHERE (obj2->>'date') BETWEEN '2022-08-13' AND '2023-08-17'
);

1.2.6 时间搜索 NOT
查的是另外三条数据源

1.2.7 在列表
SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (SELECT 1FROM jsonb_array_elements("ports") as arr1(obj1) CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)WHERE (obj2->>'nickname') IN ('ggg','fff')
);

1.2.8 在列表 NOT
查的是另外三条数据源

1.2.9 包含
SELECT * FROM "public"."tf_low_data_testUser" WHERE EXISTS (SELECT 1FROM jsonb_array_elements("ports") as arr1(obj1) CROSS JOIN jsonb_array_elements(obj1->'ports') as arr2(obj2)WHERE (obj2->'address') @> '["444"]'
);
此时使用的操作符是
->,返回值是jsonb类型

1.2.10 包含 NOT
查的是另外三条数据源

二. Postgresql 9.5和以上版本
也兼容上面的JSON查询
2.1 模糊查询
使用函数
jsonb_path_exists(可以指定JSON路径,如果是数组添加[*])的正则查询达到模糊查询的效果
-- like '%ggg%'
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "g")')
-- 左模糊 like '%g'
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "g$")')
-- 右模糊 like 'g%'
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "^g")')
-- 等值匹配
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ like_regex "^ggg$")')

同样支持
NOT
2.2 等值匹配
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ == "fff")')

同样支持
NOT
2.3 时间搜索
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].date ?(@ >= "2022-01-02" && @ <= "2023-08-02")')

同样支持
NOT
2.4 在列表
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].nickname ?(@ == "ggg" || @ == "fff")')

同样支持
NOT
2.5 包含
和
等值匹配一样
SELECT * FROM "public"."tf_low_data_testUser" WHERE jsonb_path_exists("ports", '$[*].ports[*].address ?(@ == "222")')

同样支持
NOT
相关文章:
Postgresql JSON对象和数组查询
文章目录 一. Postgresql 9.5以下版本1.1 简单查询(缺陷:数组必须指定下标,不推荐)1.1.1 模糊查询1.1.2 等值匹配1.1.3 时间搜索1.1.4 在列表1.1.5 包含 1.2 多层级JSONArray(推荐)1.2.1 模糊查询1.2.2 模糊查询 NOT1.2.3 等值匹配…...
搭配购买——并查集+01背包
Joe觉得云朵很美,决定去山上的商店买一些云朵。 商店里有 n 朵云,云朵被编号为 1,2,…,n,并且每朵云都有一个价值。但是商店老板跟他说,一些云朵要搭配来买才好,所以买一朵云则与这朵云有搭配的云都要买。但是Joe的钱有…...
JVM调优指令参数
常用命令查找文档站点:https://docs.oracle.com/javase/8/docs/technotes/tools/unix/index.html -XX:PrintFlagsInitial 输出所有参数的名称和默认值,默认不包括Diagnostic和Experimental的参数。可以配合 -XX:UnlockDiagnosticVMOptions和-XX:UnlockEx…...
数据结构入门 — 队列
本文属于数据结构专栏文章,适合数据结构入门者学习,涵盖数据结构基础的知识和内容体系,文章在介绍数据结构时会配合上动图演示,方便初学者在学习数据结构时理解和学习,了解数据结构系列专栏点击下方链接。 博客主页&am…...
MongoDB - 安装
一、Docker安装MongoDB 1. 安装 安装版本: 7.0.0 docker run -itd --name mongodb -v C:\\data\\mongodb\\data:/data/db -p 27017:27017 mongo:7.0.0 --auth-v: 将容器目录/data/db映射到本地C:\\data\\mongodb\\data目录,防止容器删除数据丢失-p: 端口映射--aut…...
Qt应用开发(基础篇)——颜色选择器 QColorDialog
一、前言 QColorDialog类继承于QDialog,是一个设计用来选择颜色的对话框部件。 对话框窗口 QDialog QColorDialog颜色选择器一般用来让用户选择颜色,比如画图工具中选择画笔的颜色、刷子的颜色等。你可以使用静态函数QColorDialog::getColor()直接显示对…...
vscode 清除全部的console.log
在放页面的大文件夹view上面右键点击在文件夹中查找 console.log.*$ 注意:要选择使用正则匹配 替换为 " " (空字符串)...
UG\NX CAM二次开发 插入工序 UF_OPER_create
文章作者:代工 来源网站:NX CAM二次开发专栏 简介: UG\NX CAM二次开发 插入工序 UF_OPER_create 效果: 代码: void MyClass::do_it() {tag_t setup_tag=NULL_TAG;UF_SETUP_ask_setup(&setup_tag);if (setup_tag==NULL_TAG){uc1601("请先初始化加工环境…...
C++指针、指针函数、函数指针、类指针
1、指针变量 #include <iostream>using namespace std;int main () {int var 20; // 实际变量的声明int *ip; // 指针变量的声明ip &var; // 在指针变量中存储 var 的地址cout << "Value of var variable: ";cout << var …...
图:最短路径问题(BFS算法,Dijkstra算法,Floyd算法)
1 .单源最短路径 1.BFS算法(无权图) 使用广度优先遍历实现一个顶点到达其他所有顶点的最短路径。 注:无权图可以视为一种特殊的带权图,只是每条边的权值都为1。 1.算法思路: 定义一个数组存储每个结点与当前的结点的最短距离,定义一个数组…...
栈和队列篇
目录 一、栈 1.栈的概念及结构 1.1栈的概念 1.2栈的结构示意图 2.栈的实现 2.1支持动态增长的栈的结构 2.2压栈(入栈) 2.3出栈 2.4支持动态增长的栈的代码实现 二、队列 1.队列的概念及结构 1.1队列的概念 1.2队列的结构示意图 2.队列的实…...
分享一个vue-slot插槽使用场景
需求再现 <el-table-column align"center" label"状态" prop"mitStatus" show-overflow-tooltip />在这里,我想对于状态进行一个三目判断,如果为0那就是进行中,否则就是已完成,期初我是这样写…...
Qt应用开发(基础篇)——进度对话框 QProgressDialog
一、前言 QProgressDialog类继承于QDialog,是Qt设计用来反馈进度的对话框。 对话框QDialog QProgressDialog提供了一个进度条,表示当前程序的某操作的执行进度,让用户知道操作依旧在激活状态,配合按钮,用户就可以随时终…...
基于SpringBoot2的后台业务管理系统
概述 SpringBoot-Plus 是一个适合大系统拆分成小系统的架构,java快速开发平台,或者是一个微服务系统。其中加入了Thymeleaf数据模板语言代替了之前的JSP页面方式。页面展示采用Layui前端框架,包含了用户管理,角色管理,…...
Jmeter(三十):并发测试(设置集合点)
集合点:让所有请求在不满足条件的时候处于等待状态。 如:我集合点设置为50,那么不满足50个请求的时候,这些请求都会集合在一起,处于等待状态,当达到50的时候,就一起执行。从而达到并发的效果。 那么Jmeter中可以通过同步定时器 Synchronizing Timer 来完成。 Number …...
Flink的checkpoint是怎么实现的?
分析&回答 Checkpoint介绍 Checkpoint容错机制是Flink可靠性的基石,可以保证Flink集群在某个算子因为某些原因(如 异常退出)出现故障时,能够将整个应用流图的状态恢复到故障之前的某一状态,保证应用流图状态的一致性。Flink的Checkpoint机制原理来自“Chandy-Lamport alg…...
ubuntu上安装nginx
这篇文章主要介绍怎么在ubuntu上安装nginx服务器,并进行一些简单的配置。 第一步:准备好一台ubuntu操作系统的虚拟机 注意:如果你还没有安装好ubuntu,个人推荐阅读以下文章完成unbutu安装,vm的版本不用刻意安装文章中…...
9. 微积分 - 导数
文章目录 导数求导实例代码演示:迭代法求解二次函数最小值阶Hi, 大家好。我是茶桁。 我们终于结束了极限和连续的折磨,开启了新的篇章。 不过不要以为我们后面的就会很容易,只是相对来说, 没有那么绕而已。 那么,我们今天开始学习「导数」。 导数 在之前的导论,也就是…...
滑动窗口系列1-达标子数组
#达标子数组# 求达标子数组的数量 * 题目:给定一个数组,求满足子数组中最大值-最小值小于等于某个数的子数组的数量 * 例如[0,1,2,3]中求子数组中最大值-最小值小于等于 2的子数组的数量 * 结果为9,因为满足条件的只有[0,0] [0,1] [0,2] [1,1] [1,2] [1…...
电视显示技术及价格成本对比(2023年)
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接:https://blog.csdn.net/zaibeijixing/article/details/132461068 ———————————————— 截止到2023年ÿ…...
边缘计算医疗风险自查APP开发方案
核心目标:在便携设备(智能手表/家用检测仪)部署轻量化疾病预测模型,实现低延迟、隐私安全的实时健康风险评估。 一、技术架构设计 #mermaid-svg-iuNaeeLK2YoFKfao {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg…...
Linux相关概念和易错知识点(42)(TCP的连接管理、可靠性、面临复杂网络的处理)
目录 1.TCP的连接管理机制(1)三次握手①握手过程②对握手过程的理解 (2)四次挥手(3)握手和挥手的触发(4)状态切换①挥手过程中状态的切换②握手过程中状态的切换 2.TCP的可靠性&…...
为什么需要建设工程项目管理?工程项目管理有哪些亮点功能?
在建筑行业,项目管理的重要性不言而喻。随着工程规模的扩大、技术复杂度的提升,传统的管理模式已经难以满足现代工程的需求。过去,许多企业依赖手工记录、口头沟通和分散的信息管理,导致效率低下、成本失控、风险频发。例如&#…...
微信小程序云开发平台MySQL的连接方式
注:微信小程序云开发平台指的是腾讯云开发 先给结论:微信小程序云开发平台的MySQL,无法通过获取数据库连接信息的方式进行连接,连接只能通过云开发的SDK连接,具体要参考官方文档: 为什么? 因为…...
vulnyx Blogger writeup
信息收集 arp-scan nmap 获取userFlag 上web看看 一个默认的页面,gobuster扫一下目录 可以看到扫出的目录中得到了一个有价值的目录/wordpress,说明目标所使用的cms是wordpress,访问http://192.168.43.213/wordpress/然后查看源码能看到 这…...
mac 安装homebrew (nvm 及git)
mac 安装nvm 及git 万恶之源 mac 安装这些东西离不开Xcode。及homebrew 一、先说安装git步骤 通用: 方法一:使用 Homebrew 安装 Git(推荐) 步骤如下:打开终端(Terminal.app) 1.安装 Homebrew…...
django blank 与 null的区别
1.blank blank控制表单验证时是否允许字段为空 2.null null控制数据库层面是否为空 但是,要注意以下几点: Django的表单验证与null无关:null参数控制的是数据库层面字段是否可以为NULL,而blank参数控制的是Django表单验证时字…...
Qt 事件处理中 return 的深入解析
Qt 事件处理中 return 的深入解析 在 Qt 事件处理中,return 语句的使用是另一个关键概念,它与 event->accept()/event->ignore() 密切相关但作用不同。让我们详细分析一下它们之间的关系和工作原理。 核心区别:不同层级的事件处理 方…...
【Kafka】Kafka从入门到实战:构建高吞吐量分布式消息系统
Kafka从入门到实战:构建高吞吐量分布式消息系统 一、Kafka概述 Apache Kafka是一个分布式流处理平台,最初由LinkedIn开发,后成为Apache顶级项目。它被设计用于高吞吐量、低延迟的消息处理,能够处理来自多个生产者的海量数据,并将这些数据实时传递给消费者。 Kafka核心特…...
二维FDTD算法仿真
二维FDTD算法仿真,并带完全匹配层,输入波形为高斯波、平面波 FDTD_二维/FDTD.zip , 6075 FDTD_二维/FDTD_31.m , 1029 FDTD_二维/FDTD_32.m , 2806 FDTD_二维/FDTD_33.m , 3782 FDTD_二维/FDTD_34.m , 4182 FDTD_二维/FDTD_35.m , 4793...
