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

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

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

select * 
from tag_test, jsonb_array_elements_text(tags) 

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

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

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

id                        |name|tags                          |
--------------------------+----+------------------------------+
com.fenbi.android.gaozhong|猿题库 |["题库", "解题", "学霸", "学渣", "考试"]|
com.fenbi.android.solar   |小猿搜题|["题库", "学霸", "学渣", "考试"]      |
 
 -- 将 tags 字段转换成 value 字段进行查询

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

id                        |name|tags                          |value|
--------------------------+----+------------------------------+-----+
com.fenbi.android.gaozhong|猿题库 |["题库", "解题", "学霸", "学渣", "考试"]|学霸   |
com.fenbi.android.solar   |小猿搜题|["题库", "学霸", "学渣", "考试"]      |学霸   |

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

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

id                        |name|tags                          |
--------------------------+----+------------------------------+
com.fenbi.android.gaozhong|猿题库 |["题库", "解题", "学霸", "学渣", "考试"]|
com.fenbi.android.solar   |小猿搜题|["题库", "学霸", "学渣", "考试"]      |

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

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

id                        |name|tags                          |
--------------------------+----+------------------------------+
com.fenbi.android.gaozhong|猿题库 |["题库", "解题", "学霸", "学渣", "考试"]|
com.fenbi.android.solar   |小猿搜题|["题库", "学霸", "学渣", "考试"]      |

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

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

id                        |name|tags                          |
--------------------------+----+------------------------------+
com.fenbi.android.gaozhong|猿题库 |["题库", "解题", "学霸", "学渣", "考试"]|

--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
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇