json pgsql 函数 操作_PostgreSQL如何使用json

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)