文章目录
PostgreSQL JSON类型常用操作
说明
根据RFC 7159[1]中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函数和操作符可以用于存储在这些数据类型中的数据
PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。
一个JSON数值可以是一个简单值（数字、字符串、true/null/false），数组，对象。下列都是合法的JSON表达式：
-- 简单标量/简单值
-- 简单值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;
-- 零个或者更多个元素的数组（元素类型可以不同）
SELECT '[1, 2, "foo", null]'::json;
-- 含有键/值对的对象
-- 注意对象的键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- 数组和对象可以任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
以上的JSON类型都可以写成JSONB类型的表达式，例如：
-- 简单标量/简单值,转化为jsonb类型
SELECT '5'::jsonb;
当一个 JSON 值被输入并且接着不做任何附加处理就输出时， json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没有意义的细节（例如空格）。例如，注意下面的不同：
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
json
-------------------------------------------------
{"bar": "baz", "balance": 7.77, "active":false}
(1 row)
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
(1 row)
值得一提的一种语义上无意义的细节是，在jsonb中数据会被按照底层 numeric类型的行为来打印。实际上，这意味着用E记号 输入的数字被打印出来时就不会有该记号，例如：
SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
json | jsonb
-----------------------+-------------------------
{"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)
JSON 基本类型和相应的PostgreSQL类型
| JSON 基本类型 | PostgreSQL类型 | 注释 |
|---|---|---|
| string | text | 不允许\u0000，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样 |
| number | numeric | 不允许NaN 和 infinity值 |
| boolean | boolean | 只接受小写true和false拼写 |
| null | (无) | SQL NULL是一个不同的概念 |
json 和 jsonb区别
json 和 jsonb数据类型接受几乎完全相同的值集合作为输入。
| 对比项 | json | jsonb |
|---|---|---|
| 特点 | json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。 | jsonb数据被存储在一种分解好的二进制格式中，因为需要做附加的转换，它在输入时要稍慢一些。但是 jsonb在处理时要快很多，因为不需要重新解析。 |
| 字符处理 | json类型存储的是输入文本的准确拷贝，存储时会空格和JSON 对象内部的键的顺序。如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（处理函数会把最后的值当作有效值）。 | jsonb不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有最后一个值会被保留。 |
| 效率 | json类型存储快，使用慢（写入快，读取慢） | jsonb类型存储稍慢，使用较快（写入慢，读取快） |
| 索引支持 | 不支持索引 | 支持索引 |
示例：
postgres=# select '{"name":"zhangsan","age":"12","name":"lisi"}'::json as json;
json
----------------------------------------------
{"name":"zhangsan","age":"12","name":"lisi"}
(1 row)
postgres=# select '{"name":"zhangsan","age":"12","name":"lisi"}'::jsonb as json;
json
-------------------------------
{"age": "12", "name": "lisi"}
(1 row)
创建测试数据
drop table if exists orders;
CREATE TABLE orders (
ID serial NOT NULL PRIMARY KEY,
info json NOT NULL
);
INSERT INTO orders (info) VALUES
('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'),
('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'),
('[{ "customer": "John Steven", "items": {"product": "Toy Car","qty": 5}},{ "customer": "Tom Hark", "items": {"product": "Diaper","qty": 7}}]'),
('{"customer":[{"name":"Jose Manuel","country":"Germany"},{"name":"John Seo","country":"Australia"}],"items":{"product":"Beer","qty":"13"}}');
内容层级显示如下
postgres=# select id,jsonb_pretty(info::jsonb) from orders;
id | jsonb_pretty
----+------------------------------------
1 | { +
| "items": { +
| "qty": 6, +
| "product": "Beer" +
| }, +
| "customer": "John Doe" +
| }
2 | { +
| "items": { +
| "qty": 24, +
| "product": "Diaper" +
| }, +
| "customer": "Lily Bush" +
| }
3 | { +
| "items": { +
| "qty": 1, +
| "product": "Toy Car" +
| }, +
| "customer": "Josh William" +
| }
4 | { +
| "items": { +
| "qty": 2, +
| "product": "Toy Train" +
| }, +
| "customer": "Mary Clark" +
| }
5 | [ +
| { +
| "items": { +
| "qty": 5, +
| "product": "Toy Car" +
| }, +
| "customer": "John Steven" +
| }, +
| { +
| "items": { +
| "qty": 7, +
| "product": "Diaper" +
| }, +
| "customer": "Tom Hark" +
| } +
| ]
6 | { +
| "items": { +
| "qty": "13", +
| "product": "Beer" +
| }, +
| "customer": [ +
| { +
| "name": "Jose Manuel",+
| "country": "Germany" +
| }, +
| { +
| "name": "John Seo", +
| "country": "Australia"+
| } +
| ] +
| }
(6 rows)
操作符
| 操作符 | 右操作数类型 | 返回类型 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|---|
-> | int | json or jsonb | 获得 JSON 数组元素（索引从 0 开始，负整数从末尾开始计） | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
-> | text | json or jsonb | 通过键获得 JSON 对象域 | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
->> | int | text | 以text形式获得 JSON 数组元素 | '[1,2,3]'::json->>2 | 3 |
->> | text | text | 以text形式获得 JSON 对象域 | '{"a":1,"b":2}'::json->>'b' | 2 |
#> | text[] | json or jsonb | 获取在指定路径的 JSON 对象 | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
#>> | text[] | text | 以text形式获取在指定路径的 JSON 对象 | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3 |
额外的jsonb操作符
| 操作符 | 右操作数类型 | 描述 | 例子 |
|---|---|---|---|
@> | jsonb | 左边的 JSON 值是否在顶层包含右边的 JSON 路径/值项？ | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
<@ | jsonb | 左边的 JSON 路径/值项是否被包含在右边的 JSON 值的顶层？ | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
? | text | 键/元素字符串是否存在于 JSON 值的顶层？ | '{"a":1, "b":2}'::jsonb ? 'b' |
| `? | ` | text[] | 这些数组字符串中的任何一个是否做为顶层键存在？ |
?& | text[] | 是否所有这些数组字符串都作为顶层键存在？ | '["a", "b"]'::jsonb ?& array['a', 'b'] |
| ` | ` | jsonb | |
- | text | 从左操作数删除键/值对或者string元素。键/值对基于它们的键值来匹配。 | '{"a": "b"}'::jsonb - 'a' |
- | text[] | 从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。 | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
- | integer | 删除具有指定索引（负值表示倒数）的数组元素。如果 顶层容器不是数组则抛出一个错误。 | '["a", "b"]'::jsonb - 1 |
#- | text[] | 删除具有指定路径的域或者元素（对于 JSON 数组，负值 表示倒数） | '["a", {"b":1}]'::jsonb #- '{1,b}' |
@? | jsonpath | JSON路径是否返回指定的JSON值的任何项目？ | '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' |
@@ | jsonpath | 返回指定的JSON路径谓词检查结果。只考虑结果的第一项。 如果结果不是布尔值，那么返回 null 。 | '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' |
注意：
||操作符将其每一个操作数的顶层的元素串接起来。它不会递归 操作。例如，如果两个操作数都是具有公共域名称的对象，结果中的域值将只是来自右手操作数的值。@?和@@@操作符会抑制以下错误：缺乏对象字段或数组元素、意外的JSON项类型和数字错误。当搜索不同结构的JSON文档集合时，这种行为可能会有帮助。
增删改查
查询
下面查询使用->操作符，查询json中所有顾客作为键：
postgres=# SELECT info -> 'customer' AS customer FROM orders where id <=4;
customer
----------------
"John Doe"
"Lily Bush"
"Josh William"
"Mary Clark"
(4 rows)
下面使用->>操作获取所有顾客作为文本：
postgres=# SELECT info ->> 'customer' AS customer FROM orders where id <=4;
customer
--------------
John Doe
Lily Bush
Josh William
Mary Clark
(4 rows)
->操作返回json对象，我们可以链式方式继续使用->>返回特定节点。举例，下面语句返回所有购买的商品：
postgres=# SELECT info -> 'items' ->> 'product' as product FROM orders where id <=4;
product
-----------
Beer
Diaper
Toy Car
Toy Train
(4 rows)
首先使用info->'item’返回json对象。然后使用info->‘item’->>'product’返回所有产品文本值。
where子句
我们能在where子句中使用json操作符过滤数据行。举例，查找买了Diaper的记录：
SELECT info ->> 'customer' AS customer FROM orders
WHERE info -> 'items' ->> 'product' = 'Diaper';
customer
-----------
Lily Bush
(1 row)
下面查询谁一次买了2个商品，语句如下：
SELECT
info ->> 'customer' AS customer,
info -> 'items' ->> 'product' AS product
FROM
orders
WHERE
CAST (info -> 'items' ->> 'qty' AS INTEGER) = 2; -- 使用cast转换qty字段值为integer类型，然后和2进行比较
customer | product
------------+-----------
Mary Clark | Toy Train
(1 row)
以上两个案例都是where子句中的精确匹配，也可以模糊匹配
-- 查询customer的值中包含john的记录
postgres=# select * from orders where info #>> '{customer}' ~* 'john';
id | info
----+------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
(1 row)
postgres=# select * from orders where info ->> 'customer' ~* 'john';
id | info
----+------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
(1 row)
根据键名查询（函数实现）
-- 查询表某个键对应的值
postgres=# select json_object_field_text(info,'customer') from orders where id <=4;
json_object_field_text
------------------------
John Doe
Lily Bush
Josh William
Mary Clark
(5 rows)
-- 查询customer中包含 lily的记录
postgres=# select * from orders where json_object_field_text(info,'customer') ~* 'lily';
id | info
----+----------------------------------------------------------------------
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
(1 row)
-- 嵌套里面的查询
select * from orders where json_object_field_text(info,'items')::json ->> 'product' ~* 'per';
id | info
----+----------------------------------------------------------------------
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
(1 row)
根据键名（属性）查询
-- 查询有 items 属性的记录
postgres=# select * from orders where info::jsonb ? 'items' and id <=4;
id | info
----+-------------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(4 rows)
-- 查询有 items 属性的记录，并且items数据不为空的数据
select * from orders where info ->> 'items' is not null;
-- 查询有 customer属性或items的记录
select * from orders where info::jsonb ?| array['customer','items'];
-- 查询既有 customer属性又有items的记录
select * from orders where info::jsonb ?& array['customer','items'];
根据键值查询记录
-- 查询customer为'John Doe'购买的产品名和具体数量（ @> 操作符表示：左侧顶层是否包含右侧）
select info -> 'items' ->> 'product' as product,info -> 'items' ->> 'qty' as qty from orders
where info::jsonb @> '{"customer":"John Doe"}';
product | qty
---------+-----
Beer | 6
(1 row)
-- 查询 customer中包含'John Doe'的记录数
select * from orders where info::jsonb -> 'customer' ? 'John Doe';
id | info
----+------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
(1 row)
-- 查询 product中包含'Toy Car'的记录
select * from orders where info::jsonb -> 'items' -> 'product' ? 'Toy Car';
id | info
----+-------------------------------------------------------------------------
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
(1 row)
order by
-- 按照商品购买数量（qty）的值降序
select * from orders where id <=4
order by (info -> 'items' ->> 'qty')::int desc;
id | info
----+-------------------------------------------------------------------------
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
(4 rows)
-- 排序+分页
select * from orders where id <=4
order by (info -> 'items' ->> 'qty')::int desc limit 2 offset 1;
id | info
----+-------------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(2 rows)
-- 按照顾客（customer）的值排序
select * from orders where id <=4
order by info ->> 'customer';
id | info
----+-------------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(4 rows)
-- 按照顾客（customer）的值排序降序，再按照商品购买数量（qty）的值升序
select * from orders where id <=4
order by info ->> 'customer' desc,(info -> 'items' ->> 'qty')::int asc;
id | info
----+-------------------------------------------------------------------------
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
(4 rows)
json 数据的聚集函数
我们能对json数据使用聚集函数，如min,max,average,sum等。举例，下面语句返回最小数量，最大数量、平均数量以及总数量。
SELECT
MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
FROM orders where id <=4;
min | max | sum | avg
-----+-----+-----+--------------------
1 | 24 | 33 | 8.2500000000000000
(1 row)
多表Join
创建表插入数据
create table employees(info json);
insert into employees values
('{"employee_id":101,"name":"Steven","hire_date":"1990-01-01","department_id":"10"}'),
('{"employee_id":102,"name":"Bruce","hire_date":"1993-01-01","department_id":"20"}'),
('{"employee_id":103,"name":"Nancy","hire_date":"1989-01-01","department_id":"30"}');
create table departments(info json);
insert into departments values
('{"department_id":10,"department_name":"Administration"}'),
('{"department_id":20,"department_name":"Marketing"}'),
('{"department_id":30,"department_name":"Purchasing"}');
关联employees和departments查询员工的姓名和部门
-- 查询employee_id 为101的员工的姓名和部门
select t1.info ->> 'name' as emp_name,t2.info ->> 'department_name' as dep_name
from employees t1 join departments t2
on t1.info ->> 'department_id' = t2.info ->> 'department_id'
where (t1.info ->> 'employee_id')::int = 101;
emp_name | dep_name
----------+----------------
Steven | Administration
(1 row)
增加JSON字段
1、jsonb键/值追加可通过||操作符
-- jsonb类型可以直接使用 || 操作符
update orders set info = info::jsonb||'{"remark":"The most important customer!"}'::jsonb
where (info -> 'items' ->> 'qty')::int = (select max((info -> 'items' ->> 'qty')::int) from orders);
2、jsonb_set函数
update orders set info = jsonb_set(info::jsonb,'{remark}':'"The most important customer!"')
where (info -> 'items' ->> 'qty')::int = (select max((info -> 'items' ->> 'qty')::int) from orders);
-- 更新前表信息
postgres=# select * from orders where id <=4;
id | info
----+-------------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(4 rows)
-- 更新后表信息
postgres=# select * from orders where id <=4;
id | info
----+----------------------------------------------------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
2 | {"items": {"qty": 24, "product": "Diaper"}, "remark": "The most important customer!", "customer": "Lily Bush"}
(4 rows)
删除JSON字段
1、操作符-
update orders set info = (info::jsonb - 'remark')::json
where (info -> 'items' ->> 'qty')::int = (select max((info -> 'items' ->> 'qty')::int) from orders);
postgres=# select * from orders where id <=4;
id | info
----+-------------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
2 | {"items": {"qty": 24, "product": "Diaper"}, "customer": "Lily Bush"}
(4 rows)
注意：操作符 - 用于从jsonb对象中删除键，如果类型为json需要转成jsonb才能使用，然后处理完后再转回json格式。
2、操作符#-
-- 根据键名删除
postgres=# select * from orders where id=1;
id | info
----+------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
(1 row)
-- 删除id为1记录的customer键
postgres=# update orders set info=info::jsonb #- '{customer}' where id=1;
UPDATE 1
postgres=# select * from orders where id=1;
id | info
----+------------------------------------------
1 | {"items": {"qty": 6, "product": "Beer"}}
(1 row)
-- 根据键名删除嵌套的内层
postgres=# select * from orders where id=2;
id | info
----+----------------------------------------------------------------------
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
(1 row)
postgres=# update orders set info=info::jsonb #- '{items,product}' where id=2;
UPDATE 1
postgres=# select * from orders where id=2;
id | info
----+-------------------------------------------------
2 | {"items": {"qty": 24}, "customer": "Lily Bush"}
(1 row)
-- 根据元素位置删除
postgres=# SELECT '["red","green","blue"]'::jsonb - 0;
?column?
-------------------
["green", "blue"]
(1 row)
-- 删除嵌套aliases中的位置为1的键/值
postgres=# SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
?column?
--------------------------------------------------
{"name": "James", "aliases": ["Jamie", "J Man"]}
(1 row)
修改json字段值
1、||操作符，||操作符可以连接json键，也可覆盖重复的键值
-- 修改外层值
update orders set info = info::jsonb || '{"customer":"Tom"}'::jsonb where (info ->> 'customer') = 'Josh William';
2、jsonb_set函数
-- 修改外层值
UPDATE orders
SET info = jsonb_set(info::jsonb,'{customer}','"Tom"')
where id = 1;
-- 修改内层值
UPDATE orders
SET info = jsonb_set(
info::jsonb,
'{items,qty}',
'10')
WHERE (info -> 'items' ->> 'qty')::int = 1;
JSON类型数组常见操作
本次测试使用的JSON数组数据
postgres=# select id,jsonb_pretty(info::jsonb) from orders where id>=5;
id | jsonb_pretty
----+------------------------------------
5 | [ +
| { +
| "items": { +
| "qty": 5, +
| "product": "Toy Car" +
| }, +
| "customer": "John Steven" +
| }, +
| { +
| "items": { +
| "qty": 7, +
| "product": "Diaper" +
| }, +
| "customer": "Tom Hark" +
| } +
| ]
6 | { +
| "items": { +
| "qty": "13", +
| "product": "Beer" +
| }, +
| "customer": [ +
| { +
| "name": "Jose Manuel",+
| "country": "Germany" +
| }, +
| { +
| "name": "John Seo", +
| "country": "Australia"+
| } +
| ] +
| }
(2 rows)
查询数组的长度
postgres=# select jsonb_array_length(info::jsonb) from orders where id=5;
jsonb_array_length
--------------------
2
(1 row)
postgres=# select jsonb_array_length(info::jsonb -> 'customer') from orders where id=6;
jsonb_array_length
--------------------
2
(1 row)
查询数组的指定元素（按照位置编号）
-- 查询顶层数组中的第二个元素
postgres=# select info::jsonb ->> 1 from orders where id=5;
?column?
--------------------------------------------------------------------
{"items": {"qty": 7, "product": "Diaper"}, "customer": "Tom Hark"}
(1 row)
-- 查询内层数组中的第一个元素
postgres=# select info::jsonb -> 'customer' ->> 0 from orders where id=6;
?column?
-----------------------------------------------
{"name": "Jose Manuel", "country": "Germany"}
(1 row)
将数组拆分为JSON对象
postgres=# select json_array_elements(info) from orders where id=5;
json_array_elements
------------------------------------------------------------------------
{ "customer": "John Steven", "items": {"product": "Toy Car","qty": 5}}
{ "customer": "Tom Hark", "items": {"product": "Diaper","qty": 7}}
(2 rows)
postgres=# select json_array_elements(info -> 'customer') from orders where id=6;
json_array_elements
--------------------------------------------
{"name":"Jose Manuel","country":"Germany"}
{"name":"John Seo","country":"Australia"}
(2 rows)
在以上的基础上，再根据键名获取值
-- 顶层
postgres=# select json_array_elements(info) #> '{customer}' as customer from orders where id=5;
customer
---------------
"John Steven"
"Tom Hark"
(2 rows)
postgres=# select json_array_elements(info) #>> '{customer}' as customer from orders where id=5;
customer
-------------
John Steven
Tom Hark
(2 rows)
-- 去重
select distinct customer||'' from (select json_array_elements(info) #>> '{customer}' as customer from orders where id=5) tmp;
-- 内层
postgres=# select json_array_elements(info -> 'customer') #> '{name}' as customer from orders where id=6;
customer
---------------
"Jose Manuel"
"John Seo"
(2 rows)
postgres=# select json_array_elements(info -> 'customer') #>> '{name}' as customer from orders where id=6;
customer
-------------
Jose Manuel
John Seo
(2 rows)
PostgreSQL 常用JSON 函数
JSON创建函数
| 函数 | 描述 | 例子 | 结果 |
|---|---|---|---|
to_json (anyelement) | 返回该值作为一个合法的JSON对象。数组和组合会被递归处理并且转换成数组和对象。如果输入包含一个从该类型到JSON的造型，会使用该cast函数来执行转换，否则将会产生一个JSON标量值。对于任何非数字、布尔值或空值的标量类型，会使用其文本表示，并且加上适当的引号和转义让它变成一个合法的JSON字符串。 | to_json ('Fred said "Hi."'::text) | "Fred said \"Hi.\"" |
array_to_json (anyarray [, pretty_bool]) | 返回该数组为一个JSON数组。一个多维数组会变成一个JSON数组的数组。说明如果pretty_bool为true，在第一维元素之间会增加换行。 | array_to_json ('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
row_to_json (record [, pretty_bool]) | 返回该行为一个JSON对象。说明如果pretty_bool为true，在第一级别元素之间会增加换行。 | row_to_json (row(1,'foo')) | {"f1":1,"f2":"foo"} |
row_to_json(record [, pretty_bool])
把行作为一个 JSON 对象返回。如果*pretty_bool*为真，将在第1层元素之间增加换行。
此函数常用来生成json测试数据，比如将一个普通表转换成json类型表：
create table test_t(id int,name varchar(100),age int,set varchar(10));
insert into test_t values
(1,'zhangsan',18,'male'),
(2,'lisi',19,'female'),
(3,'wangwu',18,'male');
postgres=# select * from test_t;
id | name | age | set
----+----------+-----+--------
1 | zhangsan | 18 | male
2 | lisi | 19 | female
3 | wangwu | 18 | male
(3 rows)
postgres=# select row_to_json(test_t) from test_t;
row_to_json
--------------------------------------------------
{"id":1,"name":"zhangsan","age":18,"set":"male"}
{"id":2,"name":"lisi","age":19,"set":"female"}
{"id":3,"name":"wangwu","age":18,"set":"male"}
(3 rows)
JSON处理函数
| 函数 | 返回类型 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|
json_each(json) | set of key text, value json set of key text, value jsonb | 把最外层的JSON对象展开成键/值对的集合。 | select * from json_each('{"a":"foo", "b":"bar"}') | ` key |
json_each_text(json) | set of key text, value text | 把最外层的JSON对象展开成键/值对的集合。返回值的类型是text。 | select * from json_each_text('{"a":"foo", "b":"bar"}') | ` key |
json_extract_path(from_json json, VARIADIC path_elems text[]) | json | 返回path_elems指定的JSON值。等效于#>操作符。 | json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') | {"f5":99,"f6":"foo"} |
json_extract_path_text(from_json json, VARIADIC path_elems text[]) | text | 返回path_elems指定的JSON值为文本。等效于#>>操作符。 | json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') | foo |
json_object_keys(json) | setof text | 返回最外层JSON对象中的键集合。 | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') | json_object_keys ------------------ f1 f2 |
json_populate_record(base anyelement, from_json json) | anyelement | 把Expands the object in from_json中的对象展开成一行，其中的列匹配由base定义的记录类型。 | select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}') | ` a |
json_populate_recordset(base anyelement, from_json json) | set of anyelement | 将from_json中最外层的对象数组展开成一个行集合，其中的列匹配由base定义的记录类型。 | select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') | ` a |
json_array_elements(json) | set of json | 将一个JSON数组展开成JSON值的一个集合。 | select * from json_array_elements('[1,true, [2,false]]') | value ----------- 1 true [2,false] |
json_object_field_text(json_obj json, key text) | text | 提取JSON对象中指定字段的文本值，但它只能用于直接提取顶层字段，若需要过滤嵌套字段，可以利用->和->>运算符 | ||
jsonb_pretty(from_json jsonb) | text | 把*from_json*返回成一段 缩进后的 JSON 文本。 | jsonb_pretty(‘[{“f1”:1,“f2”:null},2,null,3]’) | |
jsonb_set(target jsonb, path text[], new_value jsonb[,create_missing boolean]) | jsonb | 返回*target*，其中由 *path*指定的节用 *new_value*替换，如果 *path*指定的项不存在并且 *create_missing为真（默认为 true）则加上 new_value。正如面向路径的 操作符一样，出现在path*中的 负整数表示从 JSON 数组的末尾开始数。 | jsonb_set(‘[{“f1”:1,“f2”:null},2,null,3]’, ‘{0,f1}’,‘[2,3,4]’, false) jsonb_set(‘[{“f1”:1,“f2”:null},2]’, ‘{0,f3}’,‘[2,3,4]’) | [{“f1”:[2,3,4],“f2”:null},2,null,3] [{“f1”: 1, “f2”: null, “f3”: [2, 3, 4]}, 2] |
json_each()
json_each()函数的作用是:将最外层的JSON对象展开为一组键值对。举例：
postgres=# SELECT json_each(info) FROM orders where id <=4;
json_each
---------------------------------------------------
(customer,"""John Doe""")
(items,"{""product"": ""Beer"",""qty"": 6}")
(customer,"""Lily Bush""")
(items,"{""product"": ""Diaper"",""qty"": 24}")
(customer,"""Josh William""")
(items,"{""product"": ""Toy Car"",""qty"": 1}")
(customer,"""Mary Clark""")
(items,"{""product"": ""Toy Train"",""qty"": 2}")
(8 rows)
如果想得到一组key-value对作为文本，可以使用json_each_text()函数。
postgres=# SELECT json_each_text(info) FROM orders where id <=4;
json_each_text
---------------------------------------------------
(customer,"John Doe")
(items,"{""product"": ""Beer"",""qty"": 6}")
(customer,"Lily Bush")
(items,"{""product"": ""Diaper"",""qty"": 24}")
(customer,"Josh William")
(items,"{""product"": ""Toy Car"",""qty"": 1}")
(customer,"Mary Clark")
(items,"{""product"": ""Toy Train"",""qty"": 2}")
(8 rows)
其他示例
postgres=# SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');
key | value
-----+-------
a | foo
b | bar
(2 rows)
postgres=# SELECT json_each_text('{"a":"foo", "b":"bar"}');
json_each_text
----------------
(a,foo)
(b,bar)
(2 rows)
json_object_keys()
json_object_keys()函数可以获得json对象最外层的一组键。举例：
postgres=# SELECT json_object_keys (info->'items') FROM orders where id <=4;
json_object_keys
------------------
product
qty
product
qty
product
qty
product
qty
(8 rows)
json_typeof()
json_typeof函数返回json最外层key的数据类型作为字符串。可能是number, boolean, null, object, array, string。
-- 下面语句查询所有item的数据类型：
postgres=# SELECT json_typeof (info->'items') FROM orders where id <=4;
json_typeof
-------------
object
object
object
object
(4 rows)
-- 下面语句返回嵌套类型中qty字段的数据类型：
postgres=# SELECT json_typeof (info->'items'->'qty') FROM orders where id <=4;
json_typeof
-------------
number
number
number
number
(4 rows)
json_object_field_text(json_obj json, key text)
在 PostgreSQL 中，json_object_field_text 函数用于从 JSON 对象中提取指定键的文本值。以下是其详细用法：
语法
json_object_field_text(json_obj json, key text) → text
json_obj: 目标 JSON 对象。key: 需要提取的字段名（键）。
功能
json_object_field_text 函数从 JSON 对象中提取指定键的值，并返回该值的文本表示。如果键不存在，则返回 NULL。
使用示例
假设有以下 JSON 数据：
{ "customer": "Lily Bush", "items": {"product": "Diaper", "qty": 24} }
1. 提取顶层字段值
从 info 字段中提取 customer 的值：
SELECT json_object_field_text(info, 'customer') AS customer_name
FROM orders;
2. 提取嵌套字段值
对于嵌套字段，首先需要提取包含目标字段的对象，然后从中提取字段值。例如，提取 items 对象中的 product 值：
SELECT json_object_field_text(info, 'items') AS items_json
FROM orders;
要进一步从 items JSON 对象中提取 product 字段，可以结合 jsonb 运算符：
SELECT (json_object_field_text(info, 'items')::jsonb->>'product') AS product_name
FROM orders;
注意事项
json_object_field_text函数专用于提取 JSON 对象的顶层字段。对于嵌套字段，可能需要结合其他 JSON 操作函数和运算符。- 如果使用
jsonb类型，可以直接利用->和->>运算符来处理嵌套字段，而不必转换为text。
示例表
假设 orders 表的 info 列为 JSON 类型，以下是如何使用这些函数的示例：
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
info JSON
);
INSERT INTO orders (info) VALUES
('{"customer": "Lily Bush", "items": {"product": "Diaper", "qty": 24}}'),
('{"customer": "John Doe", "items": {"product": "Wipes", "qty": 50}}');
查询 customer 字段的值：
SELECT json_object_field_text(info, 'customer') AS customer_name
FROM orders;
提取 items 对象中的 product 值：
SELECT (json_object_field_text(info, 'items')::jsonb->>'product') AS product_name
FROM orders;
json_extract_path_text(from_json json, VARIADIC path_elems text[])
以text返回由path_elems指向的 JSON 值（等效于#>>操作符）。
select json_extract_path_text(info,'customer') from orders where id<=4;
-- 等价于
select info #>> '{customer}' from orders where id<=4;
?column?
--------------
John Doe
Lily Bush
Josh William
Mary Clark
(5 rows)
jsonb_pretty(from_json jsonb)
把*from_json*返回成一段 缩进后的 JSON 文本。
postgres=# select id,jsonb_pretty(info::jsonb) from orders where id=1;
id | jsonb_pretty
----+----------------------------
1 | { +
| "items": { +
| "qty": 6, +
| "product": "Beer" +
| }, +
| "customer": "John Doe"+
| }
(1 row)
jsonb_set
jsonb_set() 函数参数如下：
jsonb_set(target jsonb, // 需要修改的数据
path text[], // 数据路径
new_value jsonb, // 新数据
create_missing boolean default true)
如果create_missing 是true （缺省是true），并且path指定的路径在target 中不存在，那么target将包含path指定部分， new_value替换部分， 或者new_value添加部分。
-- 更新 target 第0 个元素 key 为 f1 的值，如果f1 不存在 忽略
select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
jsonb_set
---------------------------------------------
[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
-- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 创建
select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');
jsonb_set
---------------------------------------------
[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
-- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 忽略
select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]', false);
jsonb_set
---------------------------------------------
[{"f1": 1, "f2": null}, 2]
json_array_elements*
把顶层 JSON 数组扩展成一个text值集合。
postgres=# select * from json_array_elements('[1,true,[2,false]]');
value
-----------
1
true
[2,false]
(3 rows)
postgres=# select * from json_array_elements_text('["foo", "bar"]');
value
-------
foo
bar
(2 rows)
json_array_length
返回顶层json数组中的元素数量。
postgres=# select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
json_array_length
-------------------
5
(1 row)
postgres=# select json_array_length('["foo", "bar"]');
json_array_length
-------------------
2
(1 row)
JSON与JSONB读写性能测试
构建JSON、JSONB测试表
下面通过一个简单的例子测试下json、jsonb的读写性能差异，计划创建以下三张表：
user_ini：基础数据表，并插入200万测试数据；tbl_user_json:： json 数据类型表，200万数据；tbl_user_jsonb： jsonb 数据类型表，200万数据；
CREATE TABLE user_ini(
id int4,
user_id int8,
user_name varchar(64),
create_time timestamp(6) with time zone default clock_timestamp());
INSERT INTO user_ini(id,user_id,user_name)
SELECT r,round(random()*2000000), r || '_francs' FROM generate_series(1,2000000) as r;
计划使用user_ini表数据生成json、jsonb数据，创建user_ini_json、user_ini_jsonb表，如下所示：
CREATE TABLE tbl_user_json(id serial, user_info json);
CREATE TABLE tbl_user_jsonb(id serial, user_info jsonb);
JSON与JSONB表写性能测试
根据user_ini数据通过row_to_json函数向表user_ini_json插入200万json数据，如下：
postgres=# \timing
Timing is on.
postgres=# INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;
INSERT 0 2000000
Time: 15093.043 ms (00:15.093)
从以上结果看出tbl_user_json插入200万数据花了15秒左右；接着根据user_ini表数据生成200万jsonb数据并插入表tbl_user_jsonb，如下：
postgres=# INSERT INTO tbl_user_jsonb(user_info) SELECT row_to_json(user_ini)::jsonb FROM user_ini;
INSERT 0 2000000
Time: 19801.533 ms (00:19.802)
从以上看出tbl_user_jsonb表插入200万jsonb数据花了19秒左右，正好验证了json数据写入比jsonb快。
比较两表占用空间大小，如下所示
postgres=# \dt+ tbl_user_json
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------+-------+----------+--------+-------------
public | tbl_user_json | table | postgres | 281 MB |
(1 row)
postgres=# \dt+ tbl_user_jsonb
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------+-------+----------+--------+-------------
public | tbl_user_jsonb | table | postgres | 333 MB |
(1 row)
从占用空间来看，同样的数据量jsonb数据类型占用空间比json稍大。
随机查一条数据对比
postgres=# select * from tbl_user_json limit 1;
id | user_info
----+----------------------------------------------------------------------------------------------------
1 | {"id":1,"user_id":1141402,"user_name":"1_francs","create_time":"2024-08-11T01:33:57.532707+08:00"}
(1 row)
Time: 0.600 ms
postgres=# select * from tbl_user_jsonb limit 1;
id | user_info
----+-----------------------------------------------------------------------------------------------------------
1 | {"id": 1, "user_id": 1141402, "user_name": "1_francs", "create_time": "2024-08-11T01:33:57.532707+08:00"}
(1 row)
Time: 0.710 ms
JSON与JSONB表读性能测试
对于json、jsonb读性能测试我们选择基于json、jsonb键值查询的场景，例如，根据user_info字段的user_name键的值查询，如下所示：
postgres=# EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name'='1_francs';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..57053.22 rows=10000 width=143) (actual time=0.576..213.625 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_user_jsonb (cost=0.00..55053.22 rows=4167 width=143) (actual time=128.923..198.702 rows=0 loops=3)
Filter: ((user_info ->> 'user_name'::text) = '1_francs'::text)
Rows Removed by Filter: 666666
Planning Time: 0.098 ms
Execution Time: 213.656 ms
(8 rows)
postgres=# EXPLAIN ANALYZE SELECT * FROM tbl_user_json WHERE user_info->>'user_name'='1_francs';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..50401.43 rows=10000 width=113) (actual time=0.353..746.473 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_user_json (cost=0.00..48401.43 rows=4167 width=113) (actual time=481.034..726.493 rows=0 loops=3)
Filter: ((user_info ->> 'user_name'::text) = '1_francs'::text)
Rows Removed by Filter: 666666
Planning Time: 0.066 ms
Execution Time: 746.494 ms
(8 rows)
如上普通查询没有走索引的情况可以看出jsonb的读更快一点。
为更好的对比tbl_user_json、tbl_user_jsonb表基于键值查询的效率，计划根据user_info字段id键进行范围扫描对比性能，创建索引如下：
CREATE INDEX idx_gin_user_info_id ON tbl_user_json USING btree (((user_info ->> 'id')::integer));
CREATE INDEX idx_gin_user_infob_id ON tbl_user_jsonb USING btree (((user_info ->> 'id')::integer));
创建索引后对比查询性能
EXPLAIN ANALYZE SELECT id,user_info->'id',user_info->'user_name' FROM tbl_user_json
WHERE (user_info->>'id')::int4>1 AND (user_info->>'id')::int4<10000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_user_json (cost=214.93..22655.42 rows=10000 width=68) (actual time=1.538..24.549 rows=9998 loops=1)
Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
Heap Blocks: exact=173
-> Bitmap Index Scan on idx_gin_user_info_id (cost=0.00..212.43 rows=10000 width=0) (actual time=1.495..1.495 rows=9998 loops=1)
Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
Planning Time: 0.546 ms
Execution Time: 25.101 ms
(7 rows)
EXPLAIN ANALYZE SELECT id,user_info->'id',user_info->'user_name' FROM tbl_user_jsonb
WHERE (user_info->>'id')::int4>1 AND (user_info->>'id')::int4<10000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_user_jsonb (cost=214.93..24049.23 rows=10000 width=68) (actual time=0.712..7.030 rows=9998 loops=1)
Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
Heap Blocks: exact=212
-> Bitmap Index Scan on idx_gin_user_infob_id (cost=0.00..212.43 rows=10000 width=0) (actual time=0.672..0.673 rows=9998 loops=1)
Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
Planning Time: 0.276 ms
Execution Time: 7.413 ms
(7 rows)
如上user_info字段id键值在1到10000范围内的记录走了索引，而且jsonb检索效率比json快了四倍以上。
从以上两个测试看出，正好验证了 “json写入比jsonb快，但检索时比jsonb慢” 的观点，值得一提的是如果需要通过key/value进行检索，例如以下。
EXPLAIN ANALYZE SELECT * FROM tbl_user_json WHERE user_info::jsonb @> '{"user_name": "2_francs"}';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..51684.33 rows=2000 width=113) (actual time=0.355..1144.857 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_user_json (cost=0.00..50484.33 rows=833 width=113) (actual time=759.142..1140.358 rows=0 loops=3)
Filter: ((user_info)::jsonb @> '{"user_name": "2_francs"}'::jsonb)
Rows Removed by Filter: 666666
Planning Time: 0.197 ms
Execution Time: 1144.885 ms
(8 rows)
EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_francs"}';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..54169.67 rows=2000 width=143) (actual time=0.473..233.123 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_user_jsonb (cost=0.00..52969.67 rows=833 width=143) (actual time=150.780..227.646 rows=0 loops=3)
Filter: (user_info @> '{"user_name": "2_francs"}'::jsonb)
Rows Removed by Filter: 666666
Planning Time: 0.114 ms
Execution Time: 233.147 ms
(8 rows)
Time: 233.684 ms
在tbl_user_jsonb字段user_info上创建gin索引，如下所示：
CREATE INDEX idx_tbl_user_jsonb_user_Info ON tbl_user_jsonb USING gin(user_Info);
索引创建后，再次执行一下，如下所示：
EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_francs"}';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_user_jsonb (cost=59.50..6647.38 rows=2000 width=143) (actual time=0.191..0.193 rows=1 loops=1)
Recheck Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_tbl_user_jsonb_user_info (cost=0.00..59.00 rows=2000 width=0) (actual time=0.173..0.174 rows=1 loops=1)
Index Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)
Planning Time: 0.632 ms
Execution Time: 0.228 ms
(7 rows)
PostgreSQ全文检索支持JSON和JSONB
PostgreSQL全文检索简介
对于大多数应用全文检索很少放到数据库中实现，一般使用单独的全文检索引擎，例如基于SQL全文检索引擎Sphinx。PostgreSQL支持全文检索，对于规模不大的应用如果不想搭建专门的搜索引擎，PostgreSQL的全文检索也可以满足需求。
如果没有使用专门的搜索引擎，大部检索需要通过数据库like操作匹配，这种检索方式主要缺点在于：
- 不能很好的支持索引，通常需全表扫描检索数据，数据量大时检索性能很低。
- 不提供检索结果排序，当输出结果数据量非常大时表现更加明显。
PostgreSQL全文检索能有效地解决这个问题，PostgreSQL全文检索通过以下两种数据类型来实现。
1、tsvector
tsvector全文检索数据类型代表一个被优化的可以基于搜索的文档，将一串字符串转换成tsvector全文检索数据类型，如下：
postgres=# SELECT 'Hello,cat,how are u? cat is smiling! '::tsvector;
tsvector
--------------------------------------------------
'Hello,cat,how' 'are' 'cat' 'is' 'smiling!' 'u?'
(1 row)
可以看到，字符串的内容被分隔成好几段，但通过::tsvector只是做类型转换，没有进行数据标准化处理，对于英文全文检索可通过函数to_tsvector进行数据标准化，如下所示：
postgres=# SELECT to_tsvector('english','Hello cat,');
to_tsvector
-------------------
'cat':2 'hello':1
(1 row)
2、tsquery
tsquery表示一个文本查询，存储用于搜索的词，并且支持布尔操作&、|、!，将字符串转换成tsquery，如下所示：
postgres=# SELECT 'hello&cat'::tsquery;
tsquery
-----------------
'hello' & 'cat'
(1 row)
上述只是转换成tsquery类型，而并没有做标准化，使用to_tsquery函数可以执行标准化，如下所示：
postgres=# SELECT to_tsquery('hello&cat');
to_tsquery
-----------------
'hello' & 'cat'
(1 row)
一个全文检索示例如下，检索字符串是否包括hello和cat字符，本例中返回真。
postgres=# SELECT to_tsvector('english','Hello cat,how are u') @@ to_tsquery('hello&cat');
?column?
----------
t
(1 row)
检索字符串是否包含字符hello和dog，本例中返回假。
postgres=# SELECT to_tsvector('english','Hello cat,how are u') @@ to_tsquery('hello&dog');
?column?
----------
f
(1 row)
有兴趣可以测试一下tsquery的其他操作符，例如|、!等。
注意：这里使用了带双参数的to_tsvector函数，函数to_tsvector双参数的格式如下：
to_tsvector([ config regconfig , ] document text)
#本节to_tsvector函数指定了config参数为english，如果不指定config参数，则默认使用default_text_search_config参数的配置。
postgres=# select name,setting,unit,context from pg_settings where name ~* 'default_text_search_config';
name | setting | unit | context
----------------------------+--------------------+------+---------
default_text_search_config | pg_catalog.english | | user
(1 row)
英文全文检索例子
下面演示一个英文全文检索示例，创建一张测试表并插入200万测试数据，如下所示：
CREATE TABLE test_search(id int4,name text);
INSERT INTO test_search(id,name) SELECT n, n||'_francs' FROM generate_series(1,2000000) n;
执行以下SQL，查询test_search表name字段包含字符1_francs的记录。
postgres=# SELECT * FROM test_search WHERE name LIKE '1_francs';
id | name
----+----------
1 | 1_francs
(1 row)
-- 执行计划
EXPLAIN ANALYZE SELECT * FROM test_search WHERE name LIKE '1_francs';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..24166.67 rows=200 width=18) (actual time=0.423..84.066 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_search (cost=0.00..23146.67 rows=83 width=18) (actual time=50.502..77.706 rows=0 loops=3)
Filter: (name ~~ '1_francs'::text)
Rows Removed by Filter: 666666
Planning Time: 0.262 ms
Execution Time: 84.090 ms
(8 rows)
以上执行计划走了全表扫描，执行时间为84毫秒左右，性能很低，接着创建索引，如下所示
CREATE INDEX idx_gin_search ON test_search USING gin(to_tsvector('english',name));
执行以下SQL，查询test_search表name字段包含字符1_francs的记录。
postgres=# SELECT * FROM test_search WHERE to_tsvector('english',name) @@ to_tsquery('english','1_francs');
id | name
----+----------
1 | 1_francs
(1 row)
-- 执行计划
EXPLAIN ANALYZE SELECT * FROM test_search WHERE to_tsvector('english',name) @@ to_tsquery('english','1_francs');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_search (cost=36.39..240.11 rows=50 width=18) (actual time=0.129..0.131 rows=1 loops=1)
Recheck Cond: (to_tsvector('english'::regconfig, name) @@ '''1'' & ''franc'''::tsquery)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_gin_search (cost=0.00..36.38 rows=50 width=0) (actual time=0.111..0.112 rows=1 loops=1)
Index Cond: (to_tsvector('english'::regconfig, name) @@ '''1'' & ''franc'''::tsquery)
Planning Time: 0.289 ms
Execution Time: 0.157 ms
(7 rows)
创建索引后，以上查询走了索引并且执行时间下降到0.157毫秒，性能提升很大，值得一提的是如果SQL改成以下，则不走索引，如下所示：
EXPLAIN ANALYZE SELECT * FROM test_search WHERE to_tsvector(name) @@ to_tsquery('1_francs');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..440818.33 rows=50 width=18) (actual time=0.339..2869.023 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_search (cost=0.00..439813.33 rows=21 width=18) (actual time=1908.625..2864.505 rows=0 loops=3)
Filter: (to_tsvector(name) @@ to_tsquery('1_francs'::text))
Rows Removed by Filter: 666666
Planning Time: 0.160 ms
Execution Time: 2869.147 ms
(8 rows)
由于创建索引时使用的是to_tsvector('english',name)函数索引，带了两个参数，因此where条件中的to_tsvector函数带两个参数才能走索引，而to_tsvector(name)不走索引。
JSON、JSONB全文检索实践
在PostgreSQL10版本之前全文检索不支持json和jsonb数据类型，10版本的一个重要特性是全文检索支持json和jsonb数据类型，这一小节演示下10版本以后的这个新特性。
当前测试版本为12版本
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 12.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
PostgreSQL12版本与9.6版本to_tsvector函数的差异
先来看下9.6版本to_tsvector函数，如下：
[pg96@pghost1 ~]$ psql
psql (9.6.21)
Type "help" for help.
postgres=# \df *to_tsvector*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------------+------------------+---------------------+--------
pg_catalog | array_to_tsvector | tsvector | text[] | normal
pg_catalog | to_tsvector | tsvector | regconfig, text | normal
pg_catalog | to_tsvector | tsvector | text | normal
(3 rows)
从以上看出9.6版本to_tsvector函数的输入参数仅支持text、text[]数据类型，接着看下12版本的to_tsvector函数，如下所示：
[postgres@centos7 ~]$ psql
psql (12.17)
Type "help" for help.
postgres=# \df *to_tsvector*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------------+------------------+-------------------------+------
pg_catalog | array_to_tsvector | tsvector | text[] | func
pg_catalog | json_to_tsvector | tsvector | json, jsonb | func
pg_catalog | json_to_tsvector | tsvector | regconfig, json, jsonb | func
pg_catalog | jsonb_to_tsvector | tsvector | jsonb, jsonb | func
pg_catalog | jsonb_to_tsvector | tsvector | regconfig, jsonb, jsonb | func
pg_catalog | to_tsvector | tsvector | json | func
pg_catalog | to_tsvector | tsvector | jsonb | func
pg_catalog | to_tsvector | tsvector | regconfig, json | func
pg_catalog | to_tsvector | tsvector | regconfig, jsonb | func
pg_catalog | to_tsvector | tsvector | regconfig, text | func
pg_catalog | to_tsvector | tsvector | text | func
(11 rows)
从以上看出，12版本的to_tsvector函数支持的数据类型增加了json和jsonb。
JSON数据全文检索测试
创建数据生成函数
为了便于生成测试数据，创建以下两个函数用来随机生成指定长度的字符串，创建random_range(int4, int4)函数如下：
CREATE OR REPLACE FUNCTION random_range(int4, int4)
RETURNS int4
LANGUAGE SQL
AS $$
SELECT ($1 + FLOOR(($2 - $1 + 1) * random() ))::int4;
$$;
-- 接着创建random_text_simple(length int4)函数，此函数会调用random_range(int4, int4)函数。
CREATE OR REPLACE FUNCTION random_text_simple(length int4)
RETURNS text
LANGUAGE PLPGSQL
AS $$
DECLARE
possible_chars text := '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
output text := '';
i int4;
pos int4;
BEGIN
FOR i IN 1..length LOOP
pos := random_range(1, length(possible_chars));
output := output || substr(possible_chars, pos, 1);
END LOOP;
RETURN output;
END;
$$;
random_text_simple(length int4)函数可以随机生成指定长度字符串，如下示例：
postgres=# SELECT random_text_simple(3);
random_text_simple
--------------------
4dI
(1 row)
postgres=# SELECT random_text_simple(6);
random_text_simple
--------------------
3uLknl
(1 row)
创建JSON测试表
创建user_ini测试表，并通过random_text_simple(length int4)函数插入100万随机生成六位字符的字符串测试数据，如下所示：
drop table if exists user_ini;
CREATE TABLE user_ini(
id int4,
user_id int8,
user_name varchar(64),
create_time timestamp(6) with time zone default clock_timestamp());
INSERT INTO user_ini(id,user_id,user_name)
SELECT r,round(random()*1000000), random_text_simple(6) FROM generate_series(1,1000000) as r;
创建tbl_user_search_json表，并通过row_to_json函数将表user_ini行数据转换成json数据，如下所示：
CREATE TABLE tbl_user_search_json(id serial, user_info json);
INSERT INTO tbl_user_search_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;
-- 生成的数据如下：
postgres=# SELECT * FROM tbl_user_search_json LIMIT 1;
id | user_info
----+------------------------------------------------------------------------------------------------
1 | {"id":1,"user_id":185716,"user_name":"mOuXBE","create_time":"2024-08-11T03:27:46.69352+08:00"}
(1 row)
使用全文检索查询表tbl_user_search_json的user_info字段中包含KTU89H字符的记录，如下所示：
postgres=# SELECT * FROM tbl_user_search_json WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','bb7tQk');
id | user_info
-----+---------------------------------------------------------------------------------------------------
100 | {"id":100,"user_id":640314,"user_name":"bb7tQk","create_time":"2024-08-11T03:27:46.694603+08:00"}
(1 row)
-- 执行计划
EXPLAIN ANALYZE SELECT * FROM tbl_user_search_json WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','bb7tQk');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..127886.00 rows=5000 width=104) (actual time=1.415..2299.886 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_user_search_json (cost=0.00..126386.00 rows=2083 width=104) (actual time=1522.099..2287.579 rows=0 loops=3)
Filter: (to_tsvector('english'::regconfig, user_info) @@ '''bb7tqk'''::tsquery)
Rows Removed by Filter: 333333
Planning Time: 0.091 ms
Execution Time: 2299.910 ms
(8 rows)
以上SQL能正常执行说明全文检索支持json数据类型，只是上述SQL走了全表扫描性能低，执行时间为2299毫秒
创建索引
CREATE INDEX idx_gin_search_json ON tbl_user_search_json USING gin(to_tsvector('english',user_info));
索引创建后，再次执行以下SQL，如下所示：
EXPLAIN ANALYZE SELECT * FROM tbl_user_search_json WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','bb7tQk');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_user_search_json (cost=62.75..12193.30 rows=5000 width=104) (actual time=0.046..0.047 rows=1 loops=1)
Recheck Cond: (to_tsvector('english'::regconfig, user_info) @@ '''bb7tqk'''::tsquery)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_gin_search_json (cost=0.00..61.50 rows=5000 width=0) (actual time=0.024..0.024 rows=1 loops=1)
Index Cond: (to_tsvector('english'::regconfig, user_info) @@ '''bb7tqk'''::tsquery)
Planning Time: 0.439 ms
Execution Time: 0.079 ms
(7 rows)
从上述执行计划看出走了索引，并且执行时间降为0.079毫秒，性能非常不错。
索引支持
jsonb缺省的GIN操作符类支持使用@>、?、?&和?|操作符查询
JSONB类型支持GIN, BTree索引。一般情况下，我们会在JSONB类型字段上建GIN索引，语法如下：
CREATE INDEX idx_name ON table_name USING gin (idx_col);
CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_path_ops);
说明：在JSONB上创建GIN索引的方式有两种：使用默认的jsonb_ops操作符创建和使用jsonb_path_ops操作符创建。两者的区别在jsonb_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项的，而jsonb_path_ops则只为每个value创建一个索引项。
JSON 函数索引
CREATE TABLE test_json (json_type text,obj json);
insert into test_json values
('aa', '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}'),
('cc', '{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');
select obj->'f2' from test_json where json_type = 'aa';
?column?
----------
{"f3":1}
(1 row)
-- 创建函数索引
create index i on test_json (json_extract_path_text(obj, 'f4'));
select * from test_json where json_extract_path_text(obj, 'f4') = '{"f5":99,"f6":"foo"}';
json_type | obj
-----------+-------------------------------------------
aa | {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}
(1 row)
JSONB创建索引
-- 创建测试表并生成数据
CREATE TABLE test_t (id int,info json);
-- 创建随机生成字符串函数
create or replace function random_string(integer)
returns text as
$body$
select array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), '');
$body$
language sql volatile;
-- 插入数据
insert into test_t
select t.seq, ('{"a":{"a1":"a1a1", "a2":"a2a2"}, "name":"'||random_string(10)||'","b":"bbbbb"}')::json
from generate_series(1, 10000000) as t(seq);
CREATE TABLE test_t2 (id int,info jsonb);
CREATE TABLE test_t3 (id int,info jsonb);
insert into test_t2 select id, info::jsonb from test_t;
insert into test_t3 select id, info::jsonb from test_t;
-- 建立索引
CREATE INDEX idx_test2 ON test_t2 USING gin(info);
CREATE INDEX idx_test3 ON test_t3 USING gin(info jsonb_path_ops);
-- 未建索引执行
EXPLAIN ANALYZE SELECT * FROM test_t where info::jsonb @> '{"name":"FMYYFF6I5O"}';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..207373.79 rows=100000 width=70) (actual time=2.398..6762.787 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_t (cost=0.00..196373.79 rows=41667 width=70) (actual time=4496.908..6749.185 rows=0 loops=3)
Filter: ((info)::jsonb @> '{"name": "FMYYFF6I5O"}'::jsonb)
Rows Removed by Filter: 3333333
Planning Time: 0.069 ms
Execution Time: 6762.834 ms
(8 rows)
-- 使用jsonb_ops操作符创建索引执行
EXPLAIN ANALYZE SELECT * FROM test_t2 where info @> '{"name":"FMYYFF6I5O"}';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_t2 (cost=51.75..3802.38 rows=1000 width=88) (actual time=0.597..0.598 rows=1 loops=1)
Recheck Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_test2 (cost=0.00..51.50 rows=1000 width=0) (actual time=0.432..0.432 rows=1 loops=1)
Index Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
Planning Time: 3.766 ms
Execution Time: 0.635 ms
(7 rows)
-- 使用jsonb_path_ops操作符创建索引执行
postgres=# EXPLAIN ANALYZE SELECT * FROM test_t3 where info @> '{"name":"FMYYFF6I5O"}';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_t3 (cost=31.75..3782.38 rows=1000 width=88) (actual time=0.401..0.402 rows=1 loops=1)
Recheck Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_test3 (cost=0.00..31.50 rows=1000 width=0) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
Planning Time: 0.366 ms
Execution Time: 0.437 ms
(7 rows)
JSONB性能分析
数据准备
-- account 表 id 使用uuid 类型，需要先添加uuid-ossp模块。
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- create table
create table account (id UUID NOT NULL PRIMARY KEY default uuid_generate_v1(), content jsonb, created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP);
postgres=> \d account
Table "public.account"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+--------------------
id | uuid | | not null |uuid_generate_v1()
content | jsonb | | |
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
Indexes:
"account_pkey" PRIMARY KEY, btree (id)
content 数据结构为
content = {
"nickname": {"type": "string"},
"avatar": {"type": "string"},
"weixin": {"type": "string"},
"tags": {"type": "array", "items": {"type": "string"}},
}
批量插入数据
-- 插入100w条有 nickname avatar tags 为["python", "golang", "c"]的数据
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*20000000) || '", "avatar": "avatar_url", "tags": ["python", "golang", "c"]}')::jsonb from (select * from generate_series(1,100000)) as tmp;
-- 插入100w条有 nickname tags 为["python", "golang"]的数据
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) || '", "tags": ["python", "golang"]}')::jsonb from (select * from generate_series(1,1000000)) as tmp;
-- 插入100w条有 nickname tags 为["python"]的数据
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) || '", "tags": ["python"]}')::jsonb from (select * from generate_series(1,1000000)) as tmp;
测试查询
content 中有avatar key 的数据条数 count(*) 查询不是一个好的测试语句，就算是有索引，也只能起到过滤的作用，如果结果集比较大，查询速度还是会很慢
explain analyze select count(*) from account where content::jsonb ? 'avatar';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=45210.65..45210.66 rows=1 width=8) (actual time=148.609..159.876 rows=1 loops=1)
-> Gather (cost=45210.43..45210.64 rows=2 width=8) (actual time=148.390..159.864 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=44210.43..44210.44 rows=1 width=8) (actual time=136.871..136.872 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..44111.50 rows=39573 width=0) (actual time=0.038..134.372 rows=33333 loops=3)
Filter: (content ? 'avatar'::text)
Rows Removed by Filter: 666667
Planning Time: 1.671 ms
Execution Time: 159.937 ms
(10 rows)
content 中没有avatar key 的数据条数
explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=47200.28..47200.29 rows=1 width=8) (actual time=206.111..217.322 rows=1 loops=1)
-> Gather (cost=47200.07..47200.28 rows=2 width=8) (actual time=205.930..217.311 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=46200.07..46200.08 rows=1 width=8) (actual time=201.030..201.031 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..44111.50 rows=835427 width=0) (actual time=0.030..157.985 rows=666667 loops=3)
Filter: (NOT (content ? 'avatar'::text))
Rows Removed by Filter: 33333
Planning Time: 0.112 ms
Execution Time: 217.376 ms
(10 rows)
查询content 中nickname 为nn-194318的数据
explain analyze select * from account where content @> '{"nickname": "nn-194318"}';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..45132.50 rows=210 width=95) (actual time=197.300..208.413 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on account (cost=0.00..44111.50 rows=88 width=95) (actual time=133.899..192.681 rows=0 loops=3)
Filter: (content @> '{"nickname": "nn-194318"}'::jsonb)
Rows Removed by Filter: 700000
Planning Time: 0.206 ms
Execution Time: 208.447 ms
(8 rows)
对应的查询id 为 ‘a830f154-5711-11ef-aac7-000c29d4de9c’ 的数据
explain analyze select * from account where id='a830f154-5711-11ef-aac7-000c29d4de9c';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using account_pkey on account (cost=0.43..8.45 rows=1 width=95) (actual time=0.029..0.031 rows=1 loops=1)
Index Cond: (id = 'a830f154-5711-11ef-aac7-000c29d4de9c'::uuid)
Planning Time: 0.089 ms
Execution Time: 0.059 ms
(4 rows)
通过结果可以看到 使用 jsonb 查询和使用主键查询速度差异巨大，通过看查询分析记录可以看到，这两个语句最大的差别在于使用主键的查询用到了索引，而content nickname 的查询没有索引可以使用。 接下来测试一下使用索引时的查询速度。
索引
JSONB 最常用的是GIN 索引，GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。
jsonb的默认 GIN 操作符类支持使用顶层键存在运算符?、?&以及?| 操作符和路径/值存在运算符@>的查询。
创建默认索引
CREATE INDEX idxgin ON account USING GIN (content);
create table account (id UUID NOT NULL PRIMARY KEY default uuid_generate_v1(), content jsonb, created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP);
非默认的 GIN 操作符类jsonb_path_ops只支持索引@>操作符。
-- 创建指定路径的索引
CREATE INDEX idxginp ON account USING GIN (content jsonb_path_ops);
查询优化
创建默认索引
-- 创建简单索引
create index ix_account_content on account USING GIN (content);
现在下面这样的查询就能使用该索引：
-- content 中有avatar key 的数据条数
explain analyze select count(*) from account where content::jsonb ? 'avatar';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=35490.68..35490.69 rows=1 width=8) (actual time=42.619..42.621 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=892.06..35253.24 rows=94975 width=0) (actual time=8.532..36.016 rows=100000 loops=1)
Recheck Cond: (content ? 'avatar'::text)
Heap Blocks: exact=2041
-> Bitmap Index Scan on ix_account_content (cost=0.00..868.31 rows=94975 width=0) (actual time=8.217..8.218 rows=100000 loops=1)
Index Cond: (content ? 'avatar'::text)
Planning Time: 0.495 ms
Execution Time: 42.752 ms
(8 rows)
和之前没有添加索引时速度提升了3倍。
-- 查询content 中nickname 为nn-194318的数据
explain analyze select * from account where content@>'{"nickname": "nn-194318"}';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on account (cost=37.63..830.13 rows=210 width=95) (actual time=0.109..0.110 rows=1 loops=1)
Recheck Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on ix_account_content (cost=0.00..37.58 rows=210 width=0) (actual time=0.098..0.099 rows=1 loops=1)
Index Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)
Planning Time: 0.140 ms
Execution Time: 0.147 ms
(7 rows)
这个查询效率提升更明显，竟然比使用主键还要高效。
但是下面这种查询并不能使用索引：
-- 查询content 中不存在 avatar key 的数据条数
explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=47200.28..47200.29 rows=1 width=8) (actual time=221.257..232.061 rows=1 loops=1)
-> Gather (cost=47200.07..47200.28 rows=2 width=8) (actual time=221.052..232.049 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=46200.07..46200.08 rows=1 width=8) (actual time=215.582..215.583 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..44111.50 rows=835427 width=0) (actual time=0.033..170.202 rows=666667 loops=3)
Filter: (NOT (content ? 'avatar'::text))
Rows Removed by Filter: 33333
Planning Time: 0.120 ms
Execution Time: 232.136 ms
(10 rows)
该索引也不能被用于下面这样的查询，因为尽管操作符? 是可索引的，但它不能直接被应用于被索引列content：
explain analyze select count(1) from account where content -> 'tags' ? 'c';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=47321.09..47321.10 rows=1 width=8) (actual time=185.699..196.680 rows=1 loops=1)
-> Gather (cost=47320.88..47321.08 rows=2 width=8) (actual time=185.500..196.670 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=46320.88..46320.89 rows=1 width=8) (actual time=180.745..180.747 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..46299.00 rows=8750 width=0) (actual time=0.030..178.474 rows=33333 loops=3)
Filter: ((content -> 'tags'::text) ? 'c'::text)
Rows Removed by Filter: 666667
Planning Time: 0.108 ms
Execution Time: 196.751 ms
(10 rows)
使用表达式索引
-- 创建路径索引
create index ix_account_content_tags on account USING GIN ((content->'tags'));
-- 测试查询性能
explain analyze select count(1) from account where content -> 'tags' ? 'c';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=31745.00..31745.01 rows=1 width=8) (actual time=49.878..49.880 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=754.75..31692.50 rows=21000 width=0) (actual time=8.473..43.123 rows=100000 loops=1)
Recheck Cond: ((content -> 'tags'::text) ? 'c'::text)
Heap Blocks: exact=2041
-> Bitmap Index Scan on ix_account_content_tags (cost=0.00..749.50 rows=21000 width=0) (actual time=8.138..8.138 rows=100000 loops=1)
Index Cond: ((content -> 'tags'::text) ? 'c'::text)
Planning Time: 0.251 ms
Execution Time: 49.934 ms
(8 rows)
现在，WHERE 子句content -> 'tags' ? 'c' 将被识别为可索引操作符?在索引表达式content -> 'tags' 上的应用。
也可以利用包含查询的方式，例如：
-- 查寻 "tags" 包含数组元素 "c" 的数据的个数
explain analyze select count(1) from account where content @> '{"tags": ["c"]}';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=35506.68..35506.69 rows=1 width=8) (actual time=68.712..68.714 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=908.06..35269.24 rows=94975 width=0) (actual time=11.153..61.405 rows=100000 loops=1)
Recheck Cond: (content @> '{"tags": ["c"]}'::jsonb)
Heap Blocks: exact=2041
-> Bitmap Index Scan on ix_account_content (cost=0.00..884.31 rows=94975 width=0) (actual time=10.838..10.838 rows=100000 loops=1)
Index Cond: (content @> '{"tags": ["c"]}'::jsonb)
Planning Time: 0.257 ms
Execution Time: 68.777 ms
(8 rows)
content 列上的简单 GIN 索引（默认索引）就能支持索引查询。 但是索引将会存储content列中每一个键 和值的拷贝，表达式索引只存储tags 键下找到的数据。
虽然简单索引的方法更加灵活（因为它支持有关任意键的查询），但定向的表达式索引更小并且搜索速度比简单索引更快。 尽管jsonb_path_ops操作符类只支持用 @>操作符的查询，但它比起默认的操作符类 jsonb_ops有更客观的性能优势。一个 jsonb_path_ops索引通常也比一个相同数据上的 jsonb_ops要小得多，并且搜索的专一性更好，特 别是当查询包含频繁出现在该数据中的键时。因此，其上的搜索操作 通常比使用默认操作符类的搜索表现更好。
总结
- PG 有两种 JSON 数据类型：
json和jsonb，jsonb 性能优于json，且jsonb 支持索引。 - jsonb 写入时会处理写入数据，写入相对较慢，json会保留原始数据（包括无用的空格）
- jsonb 查询优化时一个好的方式是添加GIN 索引
- 简单索引和路径索引相比更灵活，但是占用空间多
- 路径索引比简单索引更高效，占用空间更小
PostgreSQL json 索引实践 - 检索(存在、包含、等值、范围等)加速
背景
用户在使用JSON类型时，常见的一些JSON搜索包括：
1、存在，JSON中是否存在某个KEY，某些KEY，某些KEY的任意一个
-- 存在某个KEY(TOP LEVEL)
'{"a":1, "b":2}'::jsonb ? 'b'
-- 存在所有KEY
'{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'c']
-- 存在任意key、元素
'["a", "b"]'::jsonb ?| array['a', 'b']
2、等值，JSON中是否存在指定的key:value对（支持嵌套JSON）
'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
3、包含，JSON中某个路径下的VALUE（数组）中，是否包含指定的所有元素。
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';
?column?
----------
t
(1 row)
4、相交，JSON中某个路径下的VALUE（数组）中，是否包含指定的任意元素。
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2]}}' or jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[3]}}';
?column?
----------
t
(1 row)
或(注意1,2,3需要双引号，作为text类型存储，因为操作符?| ?&暂时只支持了text[]，如果是numeric匹配不上)
postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?& array['2','3','4'];
?column?
----------
f
(1 row)
postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?| array['2','3','4'];
?column?
----------
t
(1 row)
5、范围查找，JSON中某个路径下的VALUE，是否落在某个范围内。
(js ->> 'key1' )::numeric between xx and xx
(js ->> 'key2' )::numeric between xx and xx
这些操作如何加速，或者如何使用索引加速？
一、json 索引支持
GIN的两个OPS，分别支持JSON：
The default GIN operator class for jsonb supports queries with top-level key-exists operators
?,?&and?|operators and path/value-exists operator@>.The non-default GIN operator class jsonb_path_ops supports indexing the
@>operator only.
1、支持 @> 操作符的索引如下（jsonb_path_ops只支持 @> 操作符，但是效率高）
postgres=# create table tbl(id int, js jsonb);
CREATE TABLE
postgres=# create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);
CREATE INDEX
2、支持除范围查询以外的所有查询的索引如下
postgres=# create table tbl(id int, js jsonb);
CREATE TABLE
postgres=# create index idx_tbl_1 on tbl using gin (js); -- 使用默认ops即可
CREATE INDEX
二、JSON KEY VALUE值范围查询加速
某些使用，需要对VALUE使用范围查询，比如时间(如果要建索引，请使用numeric表示，否则需要自定义immutable函数)，数值都有这些需求。
通常的做法，把范围查询的类型提取出来，创建btree表达式索引，如果有任意组合的范围查询，使用gin或rum表达式索引。
例子
create index idx1 on tbl ( ((js->>'k1')::float8) );
create index idx2 on tbl ( ((js->>'k2')::numeric) );
...
create index idxn on tbl ( ((js->>'kn')::float8) );
或
create extension btree_gin;
create index idx1 on tbl using gin( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );
或
create extension rum;
create index idx1 on tbl using rum( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );
或
create or replace function to_timestamp(text) returns timestamp as $$
select $1::timestamp;
$$ language sql strict immutable;
create index idx1 on tbl using gin( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );
-- 或
create index idx1 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );
三、索引使用例子
rum插件说明
#代码仓
https://github.com/postgrespro/rum/releases
$ git clone https://github.com/postgrespro/rum
$ cd rum
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$ make USE_PGXS=1 installcheck
$ psql DB -c "CREATE EXTENSION rum;"
创建测试表和索引
create or replace function to_timestamp(text) returns timestamp as $$
select $1::timestamp;
$$ language sql strict immutable;
create table tbl(id int, js jsonb);
create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);
create index idx_tbl_2 on tbl using gin (js);
create index idx_tbl_3 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ((js->>'k3')::numeric) );
? 操作符，匹配JSON对象中的键走索引：
postgres=# explain analyze select * from tbl where js ? 'a';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=8.01..12.02 rows=1 width=36) (actual time=0.031..0.033 rows=0 loops=1)
Recheck Cond: (js ? 'a'::text)
-> Bitmap Index Scan on idx_tbl_2 (cost=0.00..8.01 rows=1 width=0) (actual time=0.023..0.025 rows=0 loops=1)
Index Cond: (js ? 'a'::text)
Planning Time: 0.581 ms
Execution Time: 0.133 ms
(6 rows)
@> 操作符，匹配JSON对象中的键值对走索引：
postgres=# explain analyze select * from tbl where js @> '{"a":"b"}';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=8.01..12.02 rows=1 width=36) (actual time=0.017..0.017 rows=0 loops=1)
Recheck Cond: (js @> '{"a": "b"}'::jsonb)
-> Bitmap Index Scan on idx_tbl_1 (cost=0.00..8.01 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: (js @> '{"a": "b"}'::jsonb)
Planning Time: 0.132 ms
Execution Time: 0.046 ms
(6 rows)
@> 操作符，嵌套数组使用索引
postgres=# select * from tbl where js @> '{"a": {"b":"c"}}';
id | js
----+----
(0 rows)
SELECT doc->'site_name' FROM websites WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}';
jsonb
-------------------------------------------------------------------
{"a": 1, "b": {"c": [1, 2, 3], "d": ["k", "y", "z"]}, "d": "kbc"}
(1 row)
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';
?column?
----------
t
(1 row)
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,4]}}';
?column?
----------
f
(1 row)
postgres=# explain analyze select * from tbl where js @> '{"b":{"c":[2,4]}}';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=12.01..16.02 rows=1 width=36) (actual time=0.012..0.013 rows=0 loops=1)
Recheck Cond: (js @> '{"b": {"c": [2, 4]}}'::jsonb)
-> Bitmap Index Scan on idx_tbl_1 (cost=0.00..12.01 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (js @> '{"b": {"c": [2, 4]}}'::jsonb)
Planning Time: 0.111 ms
Execution Time: 0.041 ms
(6 rows)
rum扩展将键的文本值转为timestamp类型，范围查询走索引：
explain analyze select * from tbl where to_timestamp(js->>'k2') between '2018-01-01' and '2018-01-02';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=24.07..36.58 rows=6 width=36) (actual time=0.040..0.041 rows=0 loops=1)
Recheck Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text
)) <= '2018-01-02 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_tbl_3 (cost=0.00..24.06 rows=6 width=0) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::
text)) <= '2018-01-02 00:00:00'::timestamp without time zone))
Planning Time: 0.569 ms
Execution Time: 0.090 ms
(6 rows)
explain analyze select * from tbl where to_timestamp(js->>'k2') between '2018-01-01' and '2018-01-02' and ((js->>'k3')::numeric) between 1 and 200;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
---------------------
Index Scan using idx_tbl_3 on tbl (cost=36.00..44.01 rows=1 width=36) (actual time=0.033..0.034 rows=0 loops=1)
Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text))
<= '2018-01-02 00:00:00'::timestamp without time zone) AND (((js ->> 'k3'::text))::numeric >= '1'::numeric) AND (((js ->> 'k3'::text))::numeric
<= '200'::numeric))
Planning Time: 0.299 ms
Execution Time: 0.062 ms
(4 rows)
参考资料
官方文档：
https://www.postgresql.org/docs/current/functions-json.html
https://www.postgresql.org/docs/current/datatype-json.html
PostgreSQL JSON数据类型
https://blog.csdn.net/neweastsun/article/details/93345799
PostgreSQL JSON类型字段常用操作
https://blog.csdn.net/wilsonpeng3/article/details/128677263
JSON & JSONB 数据类型操作
https://help.aliyun.com/zh/analyticdb-for-postgresql/developer-reference/operations-of-json-data
PostgreSQL JSONB 使用入门
https://cloud.tencent.com/developer/article/1763846
PostgreSQL高级数据类型JSON和JSONB
https://bbs.huaweicloud.com/blogs/363682
https://emacsist.github.io/2016/10/09/postgresql中的json与jsonb/
PostgreSQL JSON函数和操作符
https://www.w3cschool.cn/postgresql13_1/postgresql13_1-n3ha3jbh.html
PostgreSQL操作JSON数据
https://blog.51cto.com/u_14441472/10771220
PostgreSQL json 索引实践 - 检索(存在、包含、等值、范围等)加速
9963
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
