美文网首页
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数据存储和条件查询

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

  • PostgreSQL和Hibernate整合

    NoSQL特性整合 要查询的数据格式: 在PostgreSQL中存储的是一个json类型的字段，查询需要使用Pos...

  • PostgreSQL即学即用之json操作

    以前存储json数据使用的都是MongoDB，后来听说PostgreSQL也可以存取json数据，借着看《Post...

  • mybatis xml中使用postgresql json查询报

    在postgresql的使用jsonb数据结构，存储的是一个json array，当查询使用包含某个字符串的时候，...

  • MYSQL JSON值查询

    mysql根据json字段的内容检索查询数据 使用 字段->'$.json属性'进行查询条件 使用json_ext...

  • JavaScript本地存储

    一、json对象（1） 认识json对象JSON对象：存储复杂数据的字面量对象① 基本语法： ② 查询数据 ③ 修...

  • mysql5.7 JSON特性

    最近在工作当中用到了mysql5.7的json特性，最初使用的是postgresql来支持json类型数据的存储...

  • 第 14 课 PostgreSQL 数据存储结构

    PostgreSQL 数据存储结构分为：逻辑储存和物理存储 逻辑存储结构是KingbaseES内部的组织和管理数据...

  • mysql json的使用

    1、json_extract 用法:某列数据存储格式为json，只查询json中的某个字段的值

  • mongodb的优缺点

    优点 面向文档存储(类JSON数据模式简单而强大) 动态查询 全索引支持,扩展到内部对象和内嵌数组 查询记录分析 ...

网友评论

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

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

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|PostGreSQL Json数据存储和条件查询|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！