添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
独立的柚子  ·  卡通古代女人-CG模型库-cg模型免费下载- ...·  1 周前    · 
纯真的木瓜  ·  資料類型摘要 | Microsoft Learn·  1 月前    · 
有爱心的香烟  ·  消息和推送通知 | flutter package·  2 月前    · 
追风的菠萝  ·  创建和使用用于文档翻译的托管标识 - ...·  4 月前    · 
大鼻子的煎鸡蛋  ·  Jmeter之Bean ...·  5 月前    · 
link管理  ›  PostGreSQL Json数据存储和条件查询 · 二三
https://idersan.github.io/2017/06/12/postgresql-json%E6%95%B0%E6%8D%AE%E5%AD%98%E5%82%A8%E5%92%8C%E6%9D%A1%E4%BB%B6%E6%9F%A5%E8%AF%A2/
愤怒的苹果
4 月前 
-- ----------------------------
-- Table structure for volunteer
-- ----------------------------
DROP TABLE IF EXISTS "public"."volunteer";
create sequence volunteer_id increment by 1 minvalue 1 no maxvalue start with 1; 
CREATE TABLE "public"."volunteer" (
"id" serial NOT NULL,
"batch" varchar(255) COLLATE "default",
"persons" json
WITH (OIDS=FALSE);
-- ----------------------------
-- Alter Sequences Owned By 
-- ----------------------------
ALTER TABLE "public"."volunteer" ALTER COLUMN id SET DEFAULT nextval('volunteer_id'); 
-- ----------------------------
-- Primary Key structure for table volunteer
-- ----------------------------
ALTER TABLE "public"."volunteer" ADD PRIMARY KEY ("id");

知识点: (1)postgresql中自增长的id创建。 (2)修改表字段语句。 (3)标准sql中table name ,column name双引号。


0x2 插入数据


INSERT INTO "public"."volunteer" ("batch","persons") VALUES('第一批名单','{
    "code": 0,
    "data": {
        "users": [
                "Age": 33,
                "ID": 1,
                "Url": "http://blog.golang.org/crankshaw@20170502235840",
                "UserName": "Crankshaw@20170502235840"
                "Age": 31,
                "ID": 2,
                "Url": "https://jack.github.io",
                "UserName": "Jack"
                "Age": 22,
                "ID": 3,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 4,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 5,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 6,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 7,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 8,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 9,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 12,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 13,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 14,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 15,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 16,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 17,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 20,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 21,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 22,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 23,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 24,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 25,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 28,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 29,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 30,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 31,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 32,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 33,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 36,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 37,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 38,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 39,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 40,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 41,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 44,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 45,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 46,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 47,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 48,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 49,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 52,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 53,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 54,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 55,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 56,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 57,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 60,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 61,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 62,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 63,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 64,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 65,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 68,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 69,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 70,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 71,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 72,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 73,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 76,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 77,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 78,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 79,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 80,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 81,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 84,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 85,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 86,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 87,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 88,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 89,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 90,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 91,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 92,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 93,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 94,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 95,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
                "Age": 22,
                "ID": 98,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 22,
                "ID": 99,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
                "Age": 21,
                "ID": 100,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
                "Age": 22,
                "ID": 101,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
                "Age": 25,
                "ID": 102,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
                "Age": 33,
                "ID": 103,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
    "msg": "Success"

0x3 条件查询json内容


查询年龄大于等于25岁以上的志愿者


SELECT row_number() OVER () as num, obj->>'UserName' as userName,obj->'Age' as age
FROM   "public"."volunteer" vol, json_array_elements(vol.persons->'data'->'users') obj
WHERE  (obj->>'Age')::int >= 25;

知识点: (1)查询结果的的row number生成。 (2)获取json对象中的子对象。 (3)转换json对象属性的数据类型。
 
推荐文章
独立的柚子  ·  卡通古代女人-CG模型库-cg模型免费下载-CG99(cg99.com)
1 周前
纯真的木瓜  ·  資料類型摘要 | Microsoft Learn
1 月前
有爱心的香烟  ·  消息和推送通知 | flutter package
2 月前
追风的菠萝  ·  创建和使用用于文档翻译的托管标识 - Azure AI services | Microsoft Learn
4 月前
大鼻子的煎鸡蛋  ·  Jmeter之Bean shell使用(二) - 贺满 - 博客园
5 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台