添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
没有腹肌的胡萝卜  ·  三体_百度百科·  1 月前    · 
没有腹肌的鞭炮  ·  李克强：不能说中国已经出现通货紧缩_评论_政 ...·  2 月前    · 
不要命的西装  ·  词条页面_百科_东方财富网·  3 月前    · 
宽容的铁链  ·  【青少年阿尔茨海默病的病因和症状是什么？ ...·  3 月前    · 
纯真的橙子  ·  南京航空航天大学·财务处·  3 月前    · 
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/
愤怒的苹果
5 月前 
-- ----------------------------
-- 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对象属性的数据类型。
 
推荐文章
没有腹肌的胡萝卜  ·  三体_百度百科
1 月前
没有腹肌的鞭炮  ·  李克强：不能说中国已经出现通货紧缩_评论_政策法规解读_政策_ ...
2 月前
不要命的西装  ·  词条页面_百科_东方财富网
3 月前
宽容的铁链  ·  【青少年阿尔茨海默病的病因和症状是什么？ ] 通俗易懂的解释
3 月前
纯真的橙子  ·  南京航空航天大学·财务处
3 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台