美文网首页
PostGreSQL Json数据存储和条件查询

PostGreSQL Json数据存储和条件查询

作者: 二三10111 | 来源:发表于2017-09-20 13:48 被阅读0次

    0x1 创建表

    创建一张志愿者的数据表,记录每批参加志愿活动的人员名单。其中人员信息保存在json字段中。

    -- ----------------------------
    -- 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对象属性的数据类型。

    相关文章

      网友评论

          本文标题:PostGreSQL Json数据存储和条件查询

          本文链接:https://www.haomeiwen.com/subject/cfcosxtx.html