postgresql 常用操作命令 JSONB操作

已于 2023-05-31 16:29:28 修改
阅读量3.2k 收藏 4
点赞数 2
文章标签： 数据库 mysql postgresql
于 2023-05-25 16:09:03 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/u010582342/article/details/130868222
版权
本文展示了如何在PostgreSQL中使用psql命令行工具，包括登录、查看数据库、选择数据库和查询表。重点讨论了JSONB字段的处理，如更新、插入、查询和删除操作，以及处理NULL值的方法。还包含了嵌套JSON的数据操作实例，如路径替换、添加和删除值。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

登录 类似于 mysql -uroot -p

psql -U postgre
postgre：用户名

查看数据库 类似于show databases

\l
在这里插入图片描述

选中数据库 类似于use hsrg

在这里插入图片描述

查看数据库表 类似于 show tables

在这里插入图片描述

HAVING

SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
必须再group by后面，相当于分组完成之后加判断条件

当jsonb字段为空时，直接使用json_set无法更新成功

首先发现，当字段为null时，怎么修改都不好使，提示成功，查询却没有。但是如果字段给输入{}时却可以更新成功，那就先给他处理下。

#可以更新、插入jsonb的单个值
UPDATE hs_report_task SET extend_param =  coalesce(extend_param::jsonb,'{}'::jsonb) || '{"array":[{"bb":"cc"}]}'::jsonb where id = '017544b27e9a427f87cfd3a757b01ed4';
#返回值如果为空，则直接返回{}，否则返回该字段值
select coalesce(extend_param::jsonb,'{}'::jsonb) from hs_report_task
#返回值，如果为空返回{}，否则返回||后面的值
select coalesce(extend_param::jsonb,'{}'::jsonb) || '{"array":[{"bb":"cc"}]}'::jsonb from hs_report_task
#直接覆盖更新，不是我要的，这种直接再见
UPDATE hs_report_task SET extend_param =  '{"dd":"kk"}'::jsonb where id = '062ecbf98ed14aa29e5875ecce36cdfe';

嵌套的JSON查询

INSERT INTO test (uid, tooldata) 
VALUES (3, '{
    "a": [{
        "b": "ccccc",
        "d": [{
            "e": "eeeee",
            "f": {
                "g": "ggggggggggg",
                "h": "HHHHHHHHHHHHHH"
            }
        }, {
            "e": "fffffffff",
            "f": {
                "g": "rrrrrrrrrr",
                "h": "66666666666" 
            }
        }]
    }, {
        "b": "ccccc",
        "d": [{
            "e": "eeeee",
            "f": {
                "g": "TTTTTTTTT",
                "h": "UUUUUUU"
            }
        }, {
            "e": "QQQQQQQQ",
            "f": {
                "g": "WWWWWWWWW",
                "h": "4444444444"
            }
        }]
    }]
}');
										
#获取items 中的 a 数组对象： 得到结果为一行的jsonb 的数组格式										
select tooldata ->> 'a' from test;  
#获取a数组对象中的d数组对象：得到结果为d的jsonb的数组格式，由于第二步中a中的数组分为两行 所以会有两行的d的jsonb的数组格式
select jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d' from test;  
#第三步中的结果为数组格式，则如同第二步操作一样，转换为jsonb 格式后拆分数组转化为多行对象 得到的结果为四行的d中的对象
select jsonb_array_elements((jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb) from test;  
#获取d对象中的f对象  得到的结果为四行的f对象
select jsonb_array_elements((jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb)->>'f' from test;  
#在获取了单层的对象后就可以结合jsonb的查询操作进行对数据的筛选  先通过子查询 将 items字段转化为多行的f对象 
select (jsonb_array_elements((jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb)->>'f')::jsonb->>'h' from test;  
#路径替换值
select jsonb_set(tooldata,'{a,0,b}','"test"') from test where uid=3
#路径添加值，添加一个JSON数据
select jsonb_set(tooldata,'{a,0,d,3}','{"test":"test1"}') from test where uid=3

依次输出
？？？在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

jsonb数组更新操作

#更新字段
UPDATE hs_report_task SET extend_param = coalesce(extend_param::jsonb,'{}'::jsonb) || '{"test":[{"uid": 1, "name": "标签名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "标签名2", "add_time": "2021-05-29 17:00:00"}]}'::jsonb where id = '062ecbf98ed14aa29e5875ecce36cdfe';

#查询字段结果
{"dd": "kk", "hr": "12", "test": [{"uid": 1, "name": "标签名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "标签名2", "add_time": "2021-05-29 17:00:00"}]}
#更新，按路径更新，这种方法不可取，毕竟需要知道去确切的路径
update test set tooldata = jsonb_set(tooldata,'{a,0,d,3}','{"test":"test1"}') from test where uid=3

#刪除路徑上的一個值
update test set tooldata = tooldata #- '{a,0,d,0,f,g}' where uid=3
#删除前
{
	"a": [{
		"b": "ccccc",
		"d": [{
			"e": "eeeee",
			"f": {
				"g": "ggggggggggg",  #删除的值
				"h": "HHHHHHHHHHHHHH"
			}
		},
		{
			"e": "fffffffff",
			"f": {
				"g": "rrrrrrrrrr",
				"h": "66666666666"
			}
		},
		{
			"test": "test4"
		},
		{
			"test": "test3"
		}]
	},
	{
		"b": "ccccc",
		"d": [{
			"e": "eeeee",
			"f": {
				"g": "TTTTTTTTT",
				"h": "UUUUUUU"
			}
		},
		{
			"e": "QQQQQQQQ",
			"f": {
				"g": "WWWWWWWWW",
				"h": "4444444444"
			}
		}]
	}]
}
#删除后
{
	"a": [{
		"b": "ccccc",
		"d": [{
			"e": "eeeee",
			"f": {
				"h": "HHHHHHHHHHHHHH"
			}
		},
		{
			"e": "fffffffff",
			"f": {
				"g": "rrrrrrrrrr",
				"h": "66666666666"
			}
		},
		{
			"test": "test4"
		},
		{
			"test": "test3"
		}]
	},
	{
		"b": "ccccc",
		"d": [{
			"e": "eeeee",
			"f": {
				"g": "TTTTTTTTT",
				"h": "UUUUUUU"
			}
		},
		{
			"e": "QQQQQQQQ",
			"f": {
				"g": "WWWWWWWWW",
				"h": "4444444444"
			}
		}]
	}]
}

#以下为一个测试代码
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-0 from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-1 from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-2 from test where uid = 3;  
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-3 from test where uid = 3;  
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-4 from test where uid = 3;  

select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb from test where uid = 3;
SELECT (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb #- '{0,f,g}' from test where uid = 3;  
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb from test where uid = 3;

通过replace实现的jsonb更新

create table purchase_order
(
    id                     serial not null primary key,
    tag                    jsonb
)


INSERT INTO purchase_order (id, tag) VALUES (4787, '[{"uid": 1, "name": "标签名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "标签名2", "add_time": "2021-05-29 17:00:00"}]');

select *from purchase_order

-- 嵌套子查询
select * from (
select jsonb_array_elements(tag) as tt from purchase_order where id =  4787) a
where  tt -> 'uid' = '1';

-- 或者这种方式
SELECT
    id,r
FROM
    purchase_order s, jsonb_array_elements(s.tag) r
WHERE
    s.id =  4787  and r->>'uid' = '2' ;

UPDATE purchase_order SET tag = tag || '[{
	"uid": 3,
	"name": "标签名3",
	"add_time": "2021-05-29 17:00:00"
}]' where  id = 4787;


SELECT tag FROM purchase_order
WHERE id =  4787 and tag @> '[{"uid": 3}]';
-- 更新操作，这种也可行，但是不是我想要的，仅仅记录。
UPDATE purchase_order AS g
   SET tag = REPLACE(tag::text, '"name": "标签名1"','"name": "标签new"')::json
 WHERE g.tag IN ( SELECT g.tag
                     FROM purchase_order AS g
                    CROSS JOIN jsonb_array_elements(g.tag) AS j
                    WHERE id = 4787 and  j ->>'uid' = '1' )

JSONB的遍历显示

CREATE TABLE person(id serial, info jsonb);
INSERT INTO person (info) VALUES ('{"num":"1","name":"张三","score":"90"}'::jsonb);
SELECT t.* FROM person, jsonb_to_record(info) AS t(num text, name text, score text);

# 更新的操作，目前还没看懂
UPDATE person t1 SET info = jsonb_set(info, array[(SELECT ORDINALITY::INT - 1 FROM person t2, jsonb_array_elements(info) WITH 
ORDINALITY WHERE t1.id = t2.id AND value->>'num' = '1')::text, 'score'::text], '"92"') WHERE id = '1'

在这里插入图片描述
在这里插入图片描述

PostgreSQL 操作json/jsonb
一碗清深的博客
07-01 1万+
PostgreSQL 9.2 中引入了对JSON类型的支持，经过几个大版本的进化，目前对JSON数字类型的支持已经比较完善。在PG中对JSON格式信息的CRUD操作，针对具体的节点建立索引，这些都可以很容易的实现。
PostgreSql常用数据类型（数字、序列、字符、时间、布尔、枚举、网络地址、json）
zhangyongze_z的博客
03-04 1万+
1、数字类型 CREATE table tm_z( n1 SMALLINT , n2 INT , n3 BIGINT , n4 DECIMAL(8,2), n5 numeric(8,2), n6 real, n7 double PRECISION, n8 SMALLserial, n9 serial, n10 bigserial); 整数型与浮点型 类型numeric可以存储非常多位的数字。建议将它用于货币金额和其它要求计算准确的数量。numeric值的计算会得到准确的结果，
PostgreSQL JSON/JSONB 查询与操作指南
miracle的专栏
12-13 1591
PostgreSQL 提供了强大的和数据类型及相关操作，适用于存储和查询半结构化数据。本文将详细介绍其常用操作。
PostgreSQL JSONJSONB 查询与操作指南
最新发布
m0_74824044的博客
02-27 750
PostgreSQL 提供了强大的和数据类型及相关操作，适用于存储和查询半结构化数据。本文将详细介绍其常用操作。: 返回 JSON 对象中的值，结果为 JSON 格式。– 返回：{“b”: 1}: 返回 JSON 对象中的值，结果为文本。– 返回：“1” (文本)– 返回：2 (JSON 格式)– 返回：“2” (文本)使用获取嵌套对象：– 返回：{“c”: 3}使用获取嵌套对象的文本值：– 返回：“3” (文本)
PostgreSQL jsonb
qq_35987023的博客
08-08 3268
在PostgreSQL中，有许多用于处理JSONB数据类型的内置函数和操作符。
postgresql的json操作
热门推荐
一名普通码农的菜地
06-26 2万+
PostgreSQL 9.3.4 Documentation Prev Up Chapter 9. Functions and Operators Next 9.15. JSON Functions and Operators Table 9-39 shows the operators that are available for use wi
【PostgreSQL】存取jsonb
wjy397的专栏
04-06 1万+
时间 2017-01-14 22:44:09  zgljl2012的个人专栏 原文  http://www.zgljl2012.com/postgresql-cun-qu-jsonb/ 主题 PostgreSQL 从PostgreSQL 9.3开始，json就成了postgres里的一种数据类型，也就是和varchar、int一样，我们表里的一个字段的类型可以为json了。
Postgresql关于JSON、JSONB的操作符、处理函数（持续更新）
走在搬砖的路上！
06-08 9062
但是，如果我们我们检索的不是JSON数组，而是一个JSON对象中的JSON对象。以上，::JSON 表示声明前面的字符串为一个JSON字符串对象，而且PostgreSQL中的JSON、JSONB对象 Key的声明必须是字符串。同时，1表示获取JSON数组中下标值为1的JSON对象。在获取一个JSON对象时，除非是JSON数组中的下标，必须要要用 { } 将JSON对象的 Key 包裹起来，否则会抛出异常。#> 表示获取指定路径的一个JSON对象，#>>表示获取指定路径的一个JSON对象的字符串。
PostgreSQL
Littleluck_Dream的博客
05-27 973
PostgreSQL是一个功能强大的 **开源** 的关系型数据库。底层基于C实现。 PostgreSQL的开源协议和Linux内核版本的**开源**协议是一样的。BDS协议，这个协议基本和MIT开源协议一样，说人话就是你可以对PostgreSQL进行一些封装，然后商业化是收费。 PostgreSQL的名字咋来的。之前叫Ingres，后面为了解决一些ingres中的一些问题，作为后面的ingres，就起名叫postgre。
PostgreSQL JSONB平等性检查与Python的实现示例
资源摘要信息:"sqlalchemy-jsonb-pg94:示例展示了 PostgresQL 的 JSONB 完美的相等性检查" 知识点: 1. SQLAlchemy: SQLAlchemy 是一个用Python编写的开源SQL工具和对象关系映射器（ORM），它为使用Python语言开发...
Windows版postgresql
01-22
备份PostgreSQL数据库常用`pg_dump`命令，例如： ```bash pg_dump mydatabase > mydatabase.dump ``` 恢复时使用`pg_restore`： ```bash pg_restore -d mydatabase mydatabase.dump ``` ## 7. 插件和扩展 ...
ASP.NET Core实现RESTful API操作PostgreSQL书籍数据库
4. Npgsql：Npgsql是PostgreSQL数据库的.NET驱动程序，它提供了一种方式，允许.NET应用程序连接到PostgreSQL数据库并执行SQL命令。Npgsql完全支持异步操作，并且可以实现高效的PostgreSQL数据库访问。 5. ...
postgresql 操作查询jsonb数据
Yuuuuuubs的博客
06-20 9704
PostgreSQL 对 jsonb 类型的筛选查询可以使用->或者->>操作符。
PostgreSQL的奥秘：全面解读JSONB——非结构化数据支持的深入探索
探索技术世界的奥秘 分享编程经验与创新见解 通过深入浅出的文章，助您在技术领域不断成长，让知识成为您前行的力量！
10-28 1463
PostgreSQL的JSONB操作符提供了一个强大的框架来管理JSON数据，并且通过战略性地使用索引，可以显著提高查询性能。通过利用倒排索引和部分索引，您可以优化PostgreSQL数据库以实现高效的数据检索，确保您的应用程序具有高性能。
PostgreSQL中jsonb类型字段操作
penn
03-14 8860
1、查询jsonb字段不为null SELECT ID, watcher :: json ->> 'processRouteWorkstations' AS workstations FROM mo_mfg_order WHERE enabled = TRUE AND mfg_order_status_enum != 'MfgOrderStatus.unReleased' AND watcher ->'processRouteWorkstations' is no
sql中 int转 numeric_PostgreSQL 中的 JSONB 性能测试
weixin_39633807的博客
11-26 622
PostgreSQL 中的 JSONB 性能测试Postgres 从 9.3 就开始支持 JSON 了，mysql 也在 5.7 加入了 JSON。对于存储非结构化数据，如果大部分的操作都是整段存储和读取，那么完全不需要构建复杂的表或者模式。很多场景下非常适合选择 JSON 数据结构。我一直非常好奇 PostgreSQL 的 JSONB 性能如何。假定我们要在一个 http 请求内查询含有 JSO...
PostGreSQL：JSON|JSONB数据类型
席木木的博客
10-29 1662
介绍PostGreSQL数据库中JSON和JSONB数据类型，包括两者的区别和基本使用
PostgreSQL使用JSONB存储和查询复杂的数据结构例子解析
IT老农民的博客
09-29 792
在PostgreSQL中使用JSONB数据类型来存储和查询复杂的数据结构是一个非常高效的方法。JSONB是PostgreSQL提供的一种二进制格式的JSON数据类型，它不仅可以存储JSON格式的数据，还提供了对这些数据的索引支持，从而加快查询速度。这些查询操作符允许你执行各种复杂的查询，包括检查键的存在、比较键值、查询嵌套结构等。通过这些方法，你可以有效地在PostgreSQL中存储和查询复杂的JSON数据结构。这将允许你在JSONB字段中搜索文本，并返回包含关键字的记录。
PostgreSQL使用jsonb进行数组增删改查的操作详解例子解析
IT老农民的博客
09-30 1106
是一种存储JSON数据的二进制格式，它提供了对JSON数组进行增删改查操作的多种函数和操作符。这些操作可以帮助你有效地对存储在PostgreSQL中的JSONB数据进行增删改查。可以查询符合JSON路径的元素，并以数组形式返回。可以扩展JSON数组为文本值的集合。操作符可以删除数组中的元素。在PostgreSQL中，可以在指定位置插入新元素。可以向数组添加新元素。可以更新数组中的元素。可以获取数组的长度。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

豆芽脚脚

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值