postgresql对json和jsonb类型字段的操作

云哥 439浏览 0评论 2021-10-29

1、SQL基础数据

DROP TABLE IF EXISTS "public"."tag_test";
CREATE TABLE "public"."tag_test" (
  "id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(64) COLLATE "pg_catalog"."default",
  "tags" jsonb
)
;
COMMENT ON COLUMN "public"."tag_test"."id" IS '主键';
COMMENT ON COLUMN "public"."tag_test"."name" IS '名称';
COMMENT ON COLUMN "public"."tag_test"."tags" IS 'tags';

-- ----------------------------
-- Records of tag_test
-- ----------------------------
INSERT INTO "public"."tag_test" VALUES ('com.csj.cet4word', '英语四级单词', '["英语四级", "四级考试", "英语单词学习"]');
INSERT INTO "public"."tag_test" VALUES ('com.duia.cet4', '英语四级君', '["英语", "四级", "CET-4", "考试"]');
INSERT INTO "public"."tag_test" VALUES ('com.duia.duiaapp', '对啊课堂', '["在线课堂", "考试", "备考"]');
INSERT INTO "public"."tag_test" VALUES ('com.example.examda', '233网校', '["资格考试", "执业考试", "四六级", "认证"]');
INSERT INTO "public"."tag_test" VALUES ('com.fenbi.android.gaozhong', '猿题库', '["题库", "解题", "学霸", "学渣", "考试"]');
INSERT INTO "public"."tag_test" VALUES ('com.fenbi.android.solar', '小猿搜题', '["题库", "学霸", "学渣", "考试"]');
INSERT INTO "public"."tag_test" VALUES ('com.jxedt', '驾校一点通', '["模拟考试", "驾考", "陪练", "驾照"]');
INSERT INTO "public"."tag_test" VALUES ('com.lexue.courser', '乐学高考', '["学习", "视频", "考试", "高考"]');
INSERT INTO "public"."tag_test" VALUES ('com.passoffice', '计算机二级office题库', '["考试"]');
INSERT INTO "public"."tag_test" VALUES ('com.runbey.ybjk', '元贝驾考', '["驾考", "驾驶", "考试", "测试", "练习"]');
INSERT INTO "public"."tag_test" VALUES ('com.tal.kaoyan', '考研帮', '["考试", "备考", "资料", "研究生"]');
INSERT INTO "public"."tag_test" VALUES ('com.yunxiao.haofenshu', '好分数', '["考试", "查分数"]');
INSERT INTO "public"."tag_test" VALUES ('zwzt.fangqiu.edu.com.zwzt', '纸条', '["考试", "高考", "中考", "素材"]');

-- ----------------------------
-- Primary Key structure for table tag_test
-- ----------------------------
ALTER TABLE "public"."tag_test" ADD CONSTRAINT "tag_test_pkey" PRIMARY KEY ("id");

-- 表数据

select * 
from tag_test

-- 将jsonb数组作为一个字段全部查询

select * 
from tag_test, jsonb_array_elements_text(tags)

-- 将 tags 字段作为文本进行 like 模糊查询

 select *
 from tag_test
 WHERE tags::text like '%学霸%'

select * 
from tag_test, jsonb_array_elements_text(tags) 
where value = '学霸'

-- 对tags 数组中的某个字进行模糊查询

select * 
from tag_test
where tags #>> '{}' like '%学霸%'

-- 对 tags 中的具体标签进行等值查询

select * 
from tag_test
where tags @> '["学霸"]'::jsonb

-- 查询 tags 中包含 "解题","学霸" 的记录

select * 
from tag_test
where tags @> '["解题","学霸"]'::jsonb

--json和jsonb共同操作符的区别
-- -> 返回类型 json； ->> 返回类型为文本；#> 返回类型 json；#>> 返回类型文本
-- @>     jsonb         左边的 JSON 值是否包含顶层右边JSON路径/值项?
-- <@    jsonb    左边的JSON路径/值是否包含在顶层右边JSON值中？
-- ?   text       字符串是否作为顶层键值存在于JSON值中？

--参考 http://www.manongjc.com/article/6726.html

最后修改于 2021-10-29 17:34:29

如果觉得我的文章对你有用，请随意赞赏

扫一扫支付