PostgreSQL如何使用json

原创多米爸比 2020-02-27

6383

Json与Jsonb

PG里json是文本方式存储;
PG里jsonb是二进制存储，存储时会删除空格以及重复的键值对(保留后插入的键值);
PG里jsonb存储时已经解析好了结构，也支持索引，所有键值对已排好序;
PG里jsonb的操作有丰富的原生函数，以jsonb开头

Jsquery

JsQuery是PG里的一个扩展，支持json的查询，使用GIN索引;
https://github.com/postgrespro/jsquery

SQL/JSON Path Language

PG12支持，基于Json元素的复杂SQL查询;
https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH

如何选择？

PG12直接用的Json Path特性，原生的函数支持非常丰富;
PG12以下版本有复杂的json查询可以使用Jsquery扩展

1. 使用json数据类型

使用json数据类型的好处是结构比较灵活，同时PG里也有很多相关的函数和操作符。
适合场景：比如大屏数据监控展示，监控指标可灵活增删，指标项内容也可以随时扩充，结合ajax异步刷新，中间应用层无感。
下面是一个简单的例子

postgres=# create table test_json(id int,info json);
CREATE TABLE

插入一条数据
postgres=# insert into test_json 
 values(1,'{"name":"jerome","familyName":"peng","company":"enmotech"}');
INSERT 0 1

查询第一条记录里json类型里的某一个key的value值
postgres=# select info ->> 'company' from test_json where id=1 ;
 ?column? 
----------
 enmotech
(1 row)
返回json对象用 ->>
返回文本值用 ->

按路径查询
'{"a": {"b":{"c": "foo"}}}'::json #>> '{a,b}'
postgres=# select '{"a": {"b":{"c": "foo"}}}'::json #>> '{a,b}';
   ?column?   
--------------
 {"c": "foo"}
(1 row)

2. 使用jsonb数据类型

使用jsonb数据类型的好处是后续处理快（插入时慢，需要做解析），但不保证插入时的键值顺序，插入解析时做了去除空格、重复键删除的处理。
某些场景不适合使用jsonb而应该用json，例如对所有键值对做Hash，传输后做验证需要保证键的顺序。

postgres=# create table test_jsonb(id int,info jsonb);
CREATE TABLE

插入一条数据
postgres=# insert into test_jsonb 
 values(1,'{"company":"enmotech",  "name":"jerome","name":"peng","career":"a dba"}');
INSERT 0 1

下面查询结果可以看到jsonb类型做了优化处理
删除了空格和重复键，同时做了排序(先根据键的长度排序，再根据键排序)
postgres=# select info from test_jsonb;
                            info                            
------------------------------------------------------------
 {"name": "peng", "career": "a dba", "company": "enmotech"}
(1 row)

包含查询
postgres=# select * from test_jsonb 
where info @> '{"company":"enmotech"}';
 id |                            info                            
----+------------------------------------------------------------
  1 | {"name": "peng", "career": "a dba", "company": "enmotech"}
(1 row)

3. 使用json path

postgres=# create table test_jsonpath(id int,info jsonb);
CREATE TABLE

insert into test_jsonpath
 values(1,'{"a1":"eggs"}');
insert into test_jsonpath
 values(2,'{"a1":"egg"}');
insert into test_jsonpath
 values(3,'{"a1":"abc","b1":"eggs"}');
insert into test_jsonpath
 values(4,'{"a1":"abc","b1":"egg"}');
insert into test_jsonpath
 values(5,'{"a1":{"a2": "eggs"}}');
insert into test_jsonpath
 values(6,'{"a1":{"a2": "egg"}}');
insert into test_jsonpath
 values(7,'{"a1":{"a2": ["eg","eggs"]}}');
insert into test_jsonpath
 values(8,'{"a1":{"a2": ["eg","egg","gg"]}}');
上面设计id为偶数的行包含全匹配关键字egg 

顶层关键字全匹配
postgres=# select * from test_jsonpath 
where info @? '$ ? (@.* == "egg")';
 id |            info            
----+----------------------------
  2 | {"a1": "egg"}
  4 | {"a1": "abc", "b1": "egg"}
(2 rows)
顶层关键字模糊查询
postgres=# select * from test_jsonpath 
where info @? '$ ? (@.* like_regex "egg")';
 id |            info             
----+-----------------------------
  1 | {"a1": "eggs"}
  2 | {"a1": "egg"}
  3 | {"a1": "abc", "b1": "eggs"}
  4 | {"a1": "abc", "b1": "egg"}
(4 rows)
顶层匹配及所有层级关键字全匹配
postgres=# select * from test_jsonpath 
where info @? '$ ? (@.** == "egg")';
 id |                info                 
----+-------------------------------------
  2 | {"a1": "egg"}
  4 | {"a1": "abc", "b1": "egg"}
  6 | {"a1": {"a2": "egg"}}
  8 | {"a1": {"a2": ["eg", "egg", "gg"]}}
(4 rows)
或者使用下面的函数
select * from test_jsonpath
where jsonb_path_exists(info, '$ ? (@.** == "egg")');

模糊查询
postgres=# select * from test_jsonpath
where jsonb_path_exists(info, '$ ? (@.** like_regex "eggs")');
 id |              info              
----+--------------------------------
  1 | {"a1": "eggs"}
  3 | {"a1": "abc", "b1": "eggs"}
  5 | {"a1": {"a2": "eggs"}}
  7 | {"a1": {"a2": ["eg", "eggs"]}}
(4 rows)

4. 使用jsquery

PG12以下，9.4及以上版本可以安装jsquery扩展进行复杂查询

$ git clone https://github.com/postgrespro/jsquery.git
$ cd jsquery
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config install
$ psql
postgres=# create extension jsquery ;
CREATE EXTENSION

上面的查询使用jsquery
postgres=# select * from test_jsonpath
 where info @@ '* = "egg"'::jsquery;
 id |                info                 
----+-------------------------------------
  2 | {"a1": "egg"}
  4 | {"a1": "abc", "b1": "egg"}
  6 | {"a1": {"a2": "egg"}}
  8 | {"a1": {"a2": ["eg", "egg", "gg"]}}
(4 rows)