PostgreSQL即学即用之json操作

0.204字数 680阅读 103

以前存储json数据使用的都是MongoDB,后来听说PostgreSQL也可以存取json数据,借着看《PostgreSQL即学即用》的机会,尝试了一下用PostgreSQL增删查改json数据。

PostgreSQL即学即用 封面

搭建学习环境

1、用docker 安装 PostgreSQL 的最新版

docker pull postgres:alpine

2、启动一个postgres容器,容器名为test-postgres,密码是mysecretpassword (⚠️仅用于测试)

docker run --name test-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres:alpine

3、启动一个临时容器连接到test-postgres,并用psql工具进行交互

docker run -it --rm --link test-postgres postgres:alpine psql -h test-postgres -U postgres

4、输入自行设定的密码

Password for user postgres:

5、进入到psql交互环境

psql (11.2)

Type "help" for help.

postgres=#


建表

本次测试的目标是把从图灵抓取的图书信息存到数据库当中。
因为抓取程序输出的就是json,所以存到postgre也是用json,方便😄

PostgreSQL支持两种json数据类型:json和jsonb,两者的主要区别在于效率:json是对输入的完整拷贝,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。效率上的差别:json类型存储快,取出慢;jsonb类型存储稍慢,取出快。

在psql交互环境下输入建表指令,books 是表名,ID是主键、book列就是json数据了(此处选择了jsonb数据类型)

CREATE TABLE books (
 ID serial NOT NULL PRIMARY KEY,
 book jsonb NOT NULL
);

输入 \d 可以看到建表的结果

 List of relations
 Schema |     Name      |   Type   |  Owner   
--------+---------------+----------+----------
 public | books         | table    | postgres
 public | books_id_seq  | sequence | postgres

插入数据

尝试插入一条数据(⚠️ 要输入的是 postgres=# 之后的内容,下同)

postgres=# INSERT INTO books (book)
postgres-# VALUES
postgres-# ('{"title": "Java攻略:Java常见问题的简单解法", "status": "上市销售", "price": "69.00 元", "ePrice": 34.99, "date": "2018-08-20", "tags": []}');

尝试插入多条数据,⚠️注意:多条数据间以逗号(,)分割,最后一条以分号(;)结尾

postgres=# INSERT INTO books (book)
postgres-# VALUES
postgres-# ('{"title": "Django企业开发实战:高效Python Web框架指南", "status": "上市销售", "price": "99.00 元", "date": "2019-01-25", "tags": []}'),
postgres-# ('{"title": "你不可不知的50个战争知识(修订版)", "status": "上市销售", "price": "39.00 元", "date": "2019-01-21", "tags": ["图灵新知", "历史", "战争"]}'),
postgres-# ('{"title": "Python科学计算最佳实践:SciPy指南", "status": "上市销售", "price": "69.00 元", "ePrice": 34.99, "date": "2018-11-30", "tags": ["python", "大数据", "数据分析", "数据科学"]}');

显示所有数据

postgres=# SELECT book FROM books;

可以看到,由于我们选用了jsonb数据类型,输出结果中json的排列顺序与输入的并不一样了。

book                                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------
 {"date": "2018-08-20", "tags": [], "price": "69.00 元", "title": "Java攻略:Java常见问题的简单解法", "ePrice": 34.99, "status": "上市销售"}
 {"date": "2019-01-25", "tags": [], "price": "99.00 元", "title": "Django企业开发实战:高效Python Web框架指南", "status": "上市销售"}
 {"date": "2019-01-21", "tags": ["图灵新知", "历史", "战争"], "price": "39.00 元", "title": "你不可不知的50个战争知识(修订版)", "status": "上市销售"}
 {"date": "2018-11-30", "tags": ["python", "大数据", "数据分析", "数据科学"], "price": "69.00 元", "title": "Python科学计算最佳实践:SciPy指南", "ePrice": 34.99, "status": "上市销售"}
(4 rows)

按条件查询

比如查询条件为 book->>'price' = '39.00 元' (字符串完全匹配),其中的 book->>'price' 就是查询路径

postgres=# SELECT book FROM books WHERE book->>'price'='39.00 元';

结果为一条

book                                                                          
-----------------------------------------------------------------------------------------------------------------------
 {"date": "2019-01-21", "tags": ["图灵新知", "历史", "战争"], "price": "39.00 元", "title": "你不可不知的50个战争知识(修订版)", "status": "上市销售"}
(1 row)

比如查询电子书价格,要把ePrice这个json字段转为浮点类型,才可以进行大小于比较

postgres=# SELECT book FROM books WHERE 
postgres-# CAST (
postgres(# book->>'ePrice' AS FLOAT4
postgres(# ) > 20;

小结

PostgreSQL在新版本里支持json数据的存储,为我们在MongoDB之外多了一种选择,有竞争总是好事👍

推荐阅读更多精彩内容