PostgreSQL(pgsql) jsonb常用sql

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

json,jsonb区别

json和jsonb,而两者唯一的区别在于效率,json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。

效率的差别:json类型存储快,查询慢,jsonb类型存储稍慢,查询较快(支持许多额外的操作符)。

json和jsonb共同操作符

操作符 返回类型 数组[1,2,3] {"a":1,"b":2,"c":3} {"a":{"b":{"c":1}},"d":[4,5,6]}
-> json select '[1,2,3]'::jsonb ->2 = 3 select '{"a":1,"b":2,"c":3}'::jsonb-> 'a'=1 select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb ->'a'={"b": {"c": 1}}
->> text select '[1,2,3]'::jsonb ->>2 = 3 select '{"a":1,"b":2,"c":3}'::jsonb->> 'a'=1 select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb ->>'a'={"b": {"c": 1}}
#> json -- -- select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb #> '{a,b}' ={"c": 1}
#>> text -- -- select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb #> '{a,b}' ={"c": 1}

jsonb额外操作符

操作符 右操作数类型 描述 例子
@> jsonb 左边的 JSON 值是否包含顶层右边JSON路径/值项? '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@ jsonb 左边的JSON路径/值是否包含在顶层右边JSON值中? '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
? text 字符串是否作为顶层键值存在于JSON值中? '{"a":1, "b":2}'::jsonb ? 'b'
?| text[] 这些数组字符串中的任何一个是否作为顶层键值存在? '{"a":1, "b":2, "c":3}'::jsonb ?|array['b',c']
?& text[] 这些数组字符串是否作为顶层键值存在? '["a", "b"]'::jsonb ?& array['a', 'b']
|| jsonb 连接两个jsonb值到新的jsonb值 '["a", "b"]'::jsonb|| '["c", "d"]'::jsonb
- text 从左操作数中删除键/值对或字符串元素。基于键值匹配键/值对。 '{"a": "b"}'::jsonb - 'a'
- integer 删除指定索引的数组元素(负整数结尾)。如果顶层容器不是一个数组,那么抛出错误。 '["a", "b"]'::jsonb - 1
#- text[] 删除指定路径的域或元素(JSON数组,负整数结尾) '["a", {"b":1}]'::jsonb #- '{1,b}'

以上,转载自:https://www.cnblogs.com/zhangfx01/p/9506219.html

 

 

创建表

CREATE TABLE "test_jsonb" (
"col" jsonb
)
WITH (OIDS=FALSE)
;

插入数据

INSERT INTO "public"."test_jsonb" ("col") VALUES ('{"age": "13", "name": "张三", "address": {"city": "北京市朝阳区", "detail": "宝能中心a座"}}');
INSERT INTO "public"."test_jsonb" ("col") VALUES ('{"age": "14", "name": "李四", "address": {"city": "北京市朝阳区", "detail": "宝能中心b座"}}');
INSERT INTO "public"."test_jsonb" ("col") VALUES ('{"age": "15", "name": "王五", "address": {"city": "北京市朝阳区", "detail": "宝能中心c座"}}');
INSERT INTO "public"."test_jsonb" ("col") VALUES ('{"age": "13", "name": "1111", "address": {"city": "北京市朝阳区", "detail": "宝能中心a座"}}');
INSERT INTO "public"."test_jsonb" ("col") VALUES ('{"age": "13", "name": "2222", "address": {"city": "北京市朝阳区", "detail": "宝能中心a座"}}');

查询单独字段

select col->>'name' from test_jsonb;

条件查询

select * from test_jsonb 
where col->>'name' = '张三';

-- like搜索
select * from test_jsonb 
where col->>'name' like '%三';

-- in搜索
select * from test_jsonb 
where col->>'name' in ('张三','1111');

排序

SELECT * FROM test_jsonb
order by col->>'age' desc,col->>'name' asc;

分页(和mysql有区别)

select * from test_jsonb 
limit 2;
select * from test_jsonb 
offset 2 limit 2;

offset = (pageNo-1)*pageSize

limit = pageSize

创建索引

-- 针对jsonb类型字段的name元素创建btree唯一索引:
CREATE UNIQUE INDEX "unique_name" ON "test_jsonb" USING btree((col -> 'name'::text));


-- 表中已经存在名称为'张三'的数据了,此时再次插入:
INSERT INTO "test_jsonb" ("col") VALUES ('{"name": "张三"}');
-- [Err] 错误:  重复键违反唯一约束"aaa"
-- DETAIL:  键值"((col -> 'name'::text))=("张三")" 已经存在

 

 

 

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看