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

