ToB企服应用市场:ToB评测及商务社交产业平台

标题: PostgreSQL JSON类型常用操作 [打印本页]

---

作者: 张国伟    时间: 2024-10-22 09:23
标题: PostgreSQL JSON类型常用操作
PostgreSQL JSON类型常用操作

说明

根据RFC 7159[1]中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 上风在于能逼迫要求每个被存储的值符合 JSON 规则。也有许多 JSON 干系的函数和操作符可以用于存储在这些数据类型中的数据
PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。
一个JSON数值可以是一个简朴值（数字、字符串、true/null/false），数组，对象。下列都是合法的JSON表达式：

1. -- 简单标量/简单值
2. -- 简单值可以是数字、带引号的字符串、true、false或者null
3. SELECT '5'::json;
5. -- 零个或者更多个元素的数组（元素类型可以不同）
6. SELECT '[1, 2, "foo", null]'::json;
8. -- 含有键/值对的对象
9. -- 注意对象的键必须总是带引号的字符串
10. SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
12. -- 数组和对象可以任意嵌套
13. SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

复制代码

以上的JSON类型都可以写成JSONB类型的表达式，例如：

1. -- 简单标量/简单值,转化为jsonb类型
2. SELECT '5'::jsonb;

复制代码

当一个 JSON 值被输入并且接着不做任何附加处理就输出时， json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没故意义的细节（例如空格）。例如，注意下面的差异：

1. SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
2.                       json                       
3. -------------------------------------------------
4. {"bar": "baz", "balance": 7.77, "active":false}
5. (1 row)
7. SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
8.                       jsonb                       
9. --------------------------------------------------
10. {"bar": "baz", "active": false, "balance": 7.77}
11. (1 row)

复制代码

值得一提的一种语义上偶然义的细节是，在jsonb中数据会被按照底层 numeric类型的活动来打印。实际上，这意味着用E记号 输入的数字被打印出来时就不会有该记号，例如：

1. SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
2.          json          |          jsonb         
3. -----------------------+-------------------------
4. {"reading": 1.230e-5} | {"reading": 0.00001230}
5. (1 row)

复制代码

JSON 根本类型和相应的PostgreSQL类型

JSON 根本类型PostgreSQL类型注释stringtext不允许\u0000，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样numbernumeric不允许NaN 和 infinity值booleanboolean只接受小写true和false拼写null(无)SQL NULL是一个差异的概念 json 和 jsonb区别

json 和 jsonb数据类型接受几乎完全相同的值聚集作为输入。
对比项jsonjsonb特点json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。jsonb数据被存储在一种分解好的二进制格式中，因为必要做附加的转换，它在输入时要稍慢一些。但是 jsonb在处理时要快许多，因为不必要重新解析。字符处理json类型存储的是输入文本的正确拷贝，存储时会空格和JSON 对象内部的键的顺序。如果一个值中的 JSON 对象包含同一个键凌驾一次，所有的键/值对都会被保留（处理函数会把末了的值当作有用值）。jsonb不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有末了一个值会被保留。效率json类型存储快，使用慢（写入快，读取慢）jsonb类型存储稍慢，使用较快（写入慢，读取快）索引支持不支持索引支持索引 示例：

1. postgres=# select '{"name":"zhangsan","age":"12","name":"lisi"}'::json as json;
2.                      json
3. ----------------------------------------------
4. {"name":"zhangsan","age":"12","name":"lisi"}
5. (1 row)
7. postgres=# select '{"name":"zhangsan","age":"12","name":"lisi"}'::jsonb as json;
8.              json
9. -------------------------------
10. {"age": "12", "name": "lisi"}
11. (1 row)

复制代码

创建测试数据

1. drop table if exists orders;
2. CREATE TABLE orders (
3.    ID serial NOT NULL PRIMARY KEY,
4.    info json NOT NULL
5. );
6. INSERT INTO orders (info) VALUES
7.     ('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'),
8.     ('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
9.     ('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
10.     ('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'),
11.         ('[{ "customer": "John Steven", "items": {"product": "Toy Car","qty": 5}},{ "customer": "Tom Hark", "items": {"product": "Diaper","qty": 7}}]'),
12.         ('{"customer":[{"name":"Jose Manuel","country":"Germany"},{"name":"John Seo","country":"Australia"}],"items":{"product":"Beer","qty":"13"}}');

复制代码

内容层级显示如下

1. postgres=# select id,jsonb_pretty(info::jsonb) from orders;
2. id |            jsonb_pretty            
3. ----+------------------------------------
4.   1 | {                                 +
5.     |     "items": {                    +
6.     |         "qty": 6,                 +
7.     |         "product": "Beer"         +
8.     |     },                            +
9.     |     "customer": "John Doe"        +
10.     | }
11.   2 | {                                 +
12.     |     "items": {                    +
13.     |         "qty": 24,                +
14.     |         "product": "Diaper"       +
15.     |     },                            +
16.     |     "customer": "Lily Bush"       +
17.     | }
18.   3 | {                                 +
19.     |     "items": {                    +
20.     |         "qty": 1,                 +
21.     |         "product": "Toy Car"      +
22.     |     },                            +
23.     |     "customer": "Josh William"    +
24.     | }
25.   4 | {                                 +
26.     |     "items": {                    +
27.     |         "qty": 2,                 +
28.     |         "product": "Toy Train"    +
29.     |     },                            +
30.     |     "customer": "Mary Clark"      +
31.     | }
32.   5 | [                                 +
33.     |     {                             +
34.     |         "items": {                +
35.     |             "qty": 5,             +
36.     |             "product": "Toy Car"  +
37.     |         },                        +
38.     |         "customer": "John Steven" +
39.     |     },                            +
40.     |     {                             +
41.     |         "items": {                +
42.     |             "qty": 7,             +
43.     |             "product": "Diaper"   +
44.     |         },                        +
45.     |         "customer": "Tom Hark"    +
46.     |     }                             +
47.     | ]
48.   6 | {                                 +
49.     |     "items": {                    +
50.     |         "qty": "13",              +
51.     |         "product": "Beer"         +
52.     |     },                            +
53.     |     "customer": [                 +
54.     |         {                         +
55.     |             "name": "Jose Manuel",+
56.     |             "country": "Germany"  +
57.     |         },                        +
58.     |         {                         +
59.     |             "name": "John Seo",   +
60.     |             "country": "Australia"+
61.     |         }                         +
62.     |     ]                             +
63.     | }
64. (6 rows)

复制代码

操作符

操作符右操作数类型返回类型描述例子例子结果->intjson or jsonb获得 JSON 数组元素（索引从 0 开始，负整数从末尾开始计）'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2{"c":"baz"}->textjson or jsonb通过键获得 JSON 对象域'{"a": {"b":"foo"}}'::json->'a'{"b":"foo"}->>inttext以text情势获得 JSON 数组元素'[1,2,3]'::json->>23->>texttext以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}'@?jsonpathJSON路径是否返回指定的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中所有顾客作为键：

1. postgres=# SELECT info -> 'customer' AS customer FROM orders where id <=4;
2.     customer
3. ----------------
4. "John Doe"
5. "Lily Bush"
6. "Josh William"
7. "Mary Clark"
8. (4 rows)

复制代码

下面使用->>操作获取所有顾客作为文本：

1. postgres=# SELECT info ->> 'customer' AS customer FROM orders where id <=4;
2.    customer
3. --------------
4. John Doe
5. Lily Bush
6. Josh William
7. Mary Clark
8. (4 rows)

复制代码

->操作返回json对象，我们可以链式方式继续使用->>返回特定节点。举例，下面语句返回所有购买的商品：

1. postgres=# SELECT info -> 'items' ->> 'product' as product FROM orders where id <=4;
2.   product
3. -----------
4. Beer
5. Diaper
6. Toy Car
7. Toy Train
8. (4 rows)

复制代码

首先使用info->'item’返回json对象。然后使用info->‘item’->>'product’返回所有产物文本值。
where子句

我们能在where子句中使用json操作符过滤数据行。举例，查找买了Diaper的记录：

1. SELECT info ->> 'customer' AS customer FROM orders
2. WHERE info -> 'items' ->> 'product' = 'Diaper';
3. customer
4. -----------
5. Lily Bush
6. (1 row)

复制代码

下面查询谁一次买了2个商品，语句如下：

1. SELECT
2.    info ->> 'customer' AS customer,
3.    info -> 'items' ->> 'product' AS product
4. FROM
5.    orders
6. WHERE
7.    CAST (info -> 'items' ->> 'qty' AS INTEGER) = 2;                        -- 使用cast转换qty字段值为integer类型，然后和2进行比较
9.   customer  |  product
10. ------------+-----------
11. Mary Clark | Toy Train
12. (1 row)

复制代码

以上两个案例都是where子句中的精确匹配，也可以模糊匹配

1. -- 查询customer的值中包含john的记录
2. postgres=# select * from orders where info #>> '{customer}' ~* 'john';
3. id |                               info
4. ----+------------------------------------------------------------------
5.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
6. (1 row)
8. postgres=# select * from orders where info ->> 'customer' ~* 'john';
9. id |                               info
10. ----+------------------------------------------------------------------
11.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
12. (1 row)

复制代码

根据键名查询（函数实现）

1. -- 查询表某个键对应的值
2. postgres=# select json_object_field_text(info,'customer') from orders where id <=4;
3. json_object_field_text
4. ------------------------
5. John Doe
6. Lily Bush
7. Josh William
8. Mary Clark
9. (5 rows)
11. -- 查询customer中包含 lily的记录
12. postgres=# select * from orders where json_object_field_text(info,'customer') ~* 'lily';
13. id |                                 info
14. ----+----------------------------------------------------------------------
15.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
16. (1 row)
18. -- 嵌套里面的查询
19. select * from orders where json_object_field_text(info,'items')::json ->> 'product' ~* 'per';
20. id |                                 info
21. ----+----------------------------------------------------------------------
22.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
23. (1 row)

复制代码

根据键名（属性）查询

1. -- 查询有 items 属性的记录
2. postgres=# select * from orders where info::jsonb ? 'items' and id <=4;
3. id |                                  info
4. ----+-------------------------------------------------------------------------
5.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
6.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
7.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
8.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
9. (4 rows)
11. -- 查询有 items 属性的记录，并且items数据不为空的数据
12. select * from orders where info ->> 'items' is not null;
14. -- 查询有 customer属性或items的记录
15. select * from orders where info::jsonb ?| array['customer','items'];
16. -- 查询既有 customer属性又有items的记录
17. select * from orders where info::jsonb ?& array['customer','items'];

复制代码

根据键值查询记录

1. -- 查询customer为'John Doe'购买的产品名和具体数量（ @> 操作符表示：左侧顶层是否包含右侧）
2. select info -> 'items' ->> 'product' as product,info -> 'items' ->> 'qty' as qty from orders
3. where info::jsonb @> '{"customer":"John Doe"}';
4. product | qty
5. ---------+-----
6. Beer    | 6
7. (1 row)
9. -- 查询 customer中包含'John Doe'的记录数
10. select * from orders where info::jsonb -> 'customer' ? 'John Doe';
11. id |                               info
12. ----+------------------------------------------------------------------
13.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
14. (1 row)
16. -- 查询 product中包含'Toy Car'的记录
17. select * from orders where info::jsonb -> 'items' -> 'product'  ? 'Toy Car';
18. id |                                  info
19. ----+-------------------------------------------------------------------------
20.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
21. (1 row)

复制代码

order by

1. -- 按照商品购买数量（qty）的值降序
2. select * from orders where id <=4
3. order by (info -> 'items' ->> 'qty')::int desc;
5. id |                                  info
6. ----+-------------------------------------------------------------------------
7.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
8.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
9.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
10.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
11. (4 rows)
13. -- 排序+分页
14. select * from orders where id <=4
15. order by (info -> 'items' ->> 'qty')::int desc limit 2 offset 1;
16. id |                                  info
17. ----+-------------------------------------------------------------------------
18.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
19.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
20. (2 rows)
22. -- 按照顾客（customer）的值排序
23. select * from orders where id <=4
24. order by info ->> 'customer';
25. id |                                  info
26. ----+-------------------------------------------------------------------------
27.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
28.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
29.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
30.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
31. (4 rows)
33. -- 按照顾客（customer）的值排序降序，再按照商品购买数量（qty）的值升序
34. select * from orders where id <=4
35. order by info ->> 'customer' desc,(info -> 'items' ->> 'qty')::int asc;
36. id |                                  info
37. ----+-------------------------------------------------------------------------
38.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
39.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
40.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
41.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
42. (4 rows)

复制代码

json 数据的聚集函数
我们能对json数据使用聚集函数，如min,max,average,sum等。举例，下面语句返回最小数量，最大数量、均匀数量以及总数量。

1. SELECT
2.     MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
3.     MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
4.     SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
5.     AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
6. FROM orders where id <=4;
8. min | max | sum |        avg
9. -----+-----+-----+--------------------
10.    1 |  24 |  33 | 8.2500000000000000
11. (1 row)

复制代码

多表Join

创建表插入数据

1. create table employees(info json);
2. insert into employees values
3. ('{"employee_id":101,"name":"Steven","hire_date":"1990-01-01","department_id":"10"}'),
4. ('{"employee_id":102,"name":"Bruce","hire_date":"1993-01-01","department_id":"20"}'),
5. ('{"employee_id":103,"name":"Nancy","hire_date":"1989-01-01","department_id":"30"}');
7. create table departments(info json);
8. insert into departments values
9. ('{"department_id":10,"department_name":"Administration"}'),
10. ('{"department_id":20,"department_name":"Marketing"}'),
11. ('{"department_id":30,"department_name":"Purchasing"}');

复制代码

关联employees和departments查询员工的姓名和部门

1. -- 查询employee_id 为101的员工的姓名和部门
2. select t1.info ->> 'name' as emp_name,t2.info ->> 'department_name' as dep_name
3. from employees t1 join departments t2
4. on t1.info ->> 'department_id' = t2.info ->> 'department_id'
5. where (t1.info ->> 'employee_id')::int = 101;
7. emp_name |    dep_name
8. ----------+----------------
9. Steven   | Administration
10. (1 row)

复制代码

增加JSON字段

1、jsonb键/值追加可通过||操作符

1. -- jsonb类型可以直接使用 || 操作符
2. update orders set info = info::jsonb||'{"remark":"The most important customer!"}'::jsonb
3. where (info -> 'items' ->> 'qty')::int = (select max((info -> 'items' ->> 'qty')::int) from orders);

复制代码

2、jsonb_set函数

1. update orders set info = jsonb_set(info::jsonb,'{remark}':'"The most important customer!"')
2. where (info -> 'items' ->> 'qty')::int = (select max((info -> 'items' ->> 'qty')::int) from orders);
4. -- 更新前表信息
5. postgres=# select * from orders where id <=4;
6. id |                                  info
7. ----+-------------------------------------------------------------------------
8.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
9.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
10.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
11.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
12. (4 rows)
14. -- 更新后表信息
15. postgres=# select * from orders where id <=4;
16. id |                                                      info
17. ----+----------------------------------------------------------------------------------------------------------------
18.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
19.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
20.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
21.   2 | {"items": {"qty": 24, "product": "Diaper"}, "remark": "The most important customer!", "customer": "Lily Bush"}
22. (4 rows)

复制代码

删除JSON字段

1、操作符-

1. update orders set info = (info::jsonb - 'remark')::json
2. where (info -> 'items' ->> 'qty')::int = (select max((info -> 'items' ->> 'qty')::int) from orders);
4. postgres=# select * from orders where id <=4;
5. id |                                  info
6. ----+-------------------------------------------------------------------------
7.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
8.   3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
9.   4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
10.   2 | {"items": {"qty": 24, "product": "Diaper"}, "customer": "Lily Bush"}
11. (4 rows)

复制代码

注意：操作符 - 用于从jsonb对象中删除键，如果类型为json必要转成jsonb才能使用，然后处理完后再转回json格式。
2、操作符#-

1. -- 根据键名删除
2. postgres=# select * from orders where id=1;
3. id |                               info
4. ----+------------------------------------------------------------------
5.   1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
6. (1 row)
7. -- 删除id为1记录的customer键
8. postgres=# update orders set info=info::jsonb #- '{customer}' where id=1;
9. UPDATE 1
10. postgres=# select * from orders where id=1;
11. id |                   info
12. ----+------------------------------------------
13.   1 | {"items": {"qty": 6, "product": "Beer"}}
14. (1 row)
17. -- 根据键名删除嵌套的内层
18. postgres=# select * from orders where id=2;
19. id |                                 info
20. ----+----------------------------------------------------------------------
21.   2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
22. (1 row)
23. postgres=# update orders set info=info::jsonb #- '{items,product}' where id=2;
24. UPDATE 1
25. postgres=# select * from orders where id=2;
26. id |                      info
27. ----+-------------------------------------------------
28.   2 | {"items": {"qty": 24}, "customer": "Lily Bush"}
29. (1 row)
32. -- 根据元素位置删除
33. postgres=# SELECT '["red","green","blue"]'::jsonb - 0;
34.      ?column?
35. -------------------
36. ["green", "blue"]
37. (1 row)
38. -- 删除嵌套aliases中的位置为1的键/值
39. postgres=# SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
40.                      ?column?
41. --------------------------------------------------
42. {"name": "James", "aliases": ["Jamie", "J Man"]}
43. (1 row)

复制代码

修改json字段值

1、||操作符，||操作符可以连接json键，也可覆盖重复的键值

1. -- 修改外层值
2. update orders set info = info::jsonb || '{"customer":"Tom"}'::jsonb where (info ->> 'customer') = 'Josh William';

复制代码

2、jsonb_set函数

1. -- 修改外层值
2. UPDATE orders
3. SET info = jsonb_set(info::jsonb,'{customer}','"Tom"')
4. where id = 1;
6. -- 修改内层值
7. UPDATE orders
8. SET info = jsonb_set(
9.     info::jsonb,
10.     '{items,qty}',
11.     '10')
12. WHERE (info -> 'items' ->> 'qty')::int = 1;

复制代码

JSON类型数组常见操作

本次测试使用的JSON数组数据

1. postgres=# select id,jsonb_pretty(info::jsonb) from orders where id>=5;
2. id |            jsonb_pretty            
3. ----+------------------------------------
4.   5 | [                                 +
5.     |     {                             +
6.     |         "items": {                +
7.     |             "qty": 5,             +
8.     |             "product": "Toy Car"  +
9.     |         },                        +
10.     |         "customer": "John Steven" +
11.     |     },                            +
12.     |     {                             +
13.     |         "items": {                +
14.     |             "qty": 7,             +
15.     |             "product": "Diaper"   +
16.     |         },                        +
17.     |         "customer": "Tom Hark"    +
18.     |     }                             +
19.     | ]
20.   6 | {                                 +
21.     |     "items": {                    +
22.     |         "qty": "13",              +
23.     |         "product": "Beer"         +
24.     |     },                            +
25.     |     "customer": [                 +
26.     |         {                         +
27.     |             "name": "Jose Manuel",+
28.     |             "country": "Germany"  +
29.     |         },                        +
30.     |         {                         +
31.     |             "name": "John Seo",   +
32.     |             "country": "Australia"+
33.     |         }                         +
34.     |     ]                             +
35.     | }
36. (2 rows)

复制代码

查询数组的长度

1. postgres=# select jsonb_array_length(info::jsonb) from orders where id=5;
2. jsonb_array_length
3. --------------------
4.                   2
5. (1 row)
7. postgres=# select jsonb_array_length(info::jsonb -> 'customer') from orders where id=6;
8. jsonb_array_length
9. --------------------
10.                   2
11. (1 row)

复制代码

查询数组的指定元素（按照位置编号）

1. -- 查询顶层数组中的第二个元素
2. postgres=# select info::jsonb ->> 1 from orders where id=5;
3.                               ?column?                              
4. --------------------------------------------------------------------
5. {"items": {"qty": 7, "product": "Diaper"}, "customer": "Tom Hark"}
6. (1 row)
8. -- 查询内层数组中的第一个元素
9. postgres=# select info::jsonb -> 'customer' ->> 0 from orders where id=6;
10.                    ?column?                    
11. -----------------------------------------------
12. {"name": "Jose Manuel", "country": "Germany"}
13. (1 row)

复制代码

将数组拆分为JSON对象

1. postgres=# select json_array_elements(info) from orders where id=5;
2.                           json_array_elements                           
3. ------------------------------------------------------------------------
4. { "customer": "John Steven", "items": {"product": "Toy Car","qty": 5}}
5. { "customer": "Tom Hark", "items": {"product": "Diaper","qty": 7}}
6. (2 rows)
8. postgres=# select json_array_elements(info -> 'customer') from orders where id=6;
9.             json_array_elements            
10. --------------------------------------------
11. {"name":"Jose Manuel","country":"Germany"}
12. {"name":"John Seo","country":"Australia"}
13. (2 rows)

复制代码

在以上的底子上，再根据键名获取值

1. -- 顶层
2. postgres=# select json_array_elements(info) #> '{customer}' as customer from orders where id=5;
3.    customer   
4. ---------------
5. "John Steven"
6. "Tom Hark"
7. (2 rows)
9. postgres=# select json_array_elements(info) #>> '{customer}' as customer from orders where id=5;
10.   customer   
11. -------------
12. John Steven
13. Tom Hark
14. (2 rows)
16. -- 去重
17. select distinct customer||'' from (select json_array_elements(info) #>> '{customer}' as customer from orders where id=5) tmp;
18.                                    
20. -- 内层
21. postgres=# select json_array_elements(info -> 'customer') #> '{name}' as customer from orders where id=6;
22.    customer   
23. ---------------
24. "Jose Manuel"
25. "John Seo"
26. (2 rows)
28. postgres=# select json_array_elements(info -> 'customer') #>> '{name}' as customer from orders where id=6;
29.   customer   
30. -------------
31. Jose Manuel
32. John Seo
33. (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类型表：

1. create table test_t(id int,name varchar(100),age int,set varchar(10));
2. insert into test_t values
3. (1,'zhangsan',18,'male'),
4. (2,'lisi',19,'female'),
5. (3,'wangwu',18,'male');
7. postgres=# select * from test_t;
8. id |   name   | age |  set
9. ----+----------+-----+--------
10.   1 | zhangsan |  18 | male
11.   2 | lisi     |  19 | female
12.   3 | wangwu   |  18 | male
13. (3 rows)
15. postgres=# select row_to_json(test_t) from test_t;
16.                    row_to_json
17. --------------------------------------------------
18. {"id":1,"name":"zhangsan","age":18,"set":"male"}
19. {"id":2,"name":"lisi","age":19,"set":"female"}
20. {"id":3,"name":"wangwu","age":18,"set":"male"}
21. (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"}')` keyjson_each_text(json)set of key text, value text把最外层的JSON对象睁开成键/值对的聚集。返回值的类型是text。select * from json_each_text('{"a":"foo", "b":"bar"}')` keyjson_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')foojson_object_keys(json)setof text返回最外层JSON对象中的键聚集。json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') json_object_keys ------------------ f1 f2json_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}')` ajson_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}]')` ajson_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对象睁开为一组键值对。举例：

1. postgres=# SELECT json_each(info) FROM orders where id <=4;
2.                      json_each
3. ---------------------------------------------------
4. (customer,"""John Doe""")
5. (items,"{""product"": ""Beer"",""qty"": 6}")
6. (customer,"""Lily Bush""")
7. (items,"{""product"": ""Diaper"",""qty"": 24}")
8. (customer,"""Josh William""")
9. (items,"{""product"": ""Toy Car"",""qty"": 1}")
10. (customer,"""Mary Clark""")
11. (items,"{""product"": ""Toy Train"",""qty"": 2}")
12. (8 rows)

复制代码

如果想得到一组key-value对作为文本，可以使用json_each_text()函数。

1. postgres=# SELECT json_each_text(info) FROM orders where id <=4;
2.                   json_each_text
3. ---------------------------------------------------
4. (customer,"John Doe")
5. (items,"{""product"": ""Beer"",""qty"": 6}")
6. (customer,"Lily Bush")
7. (items,"{""product"": ""Diaper"",""qty"": 24}")
8. (customer,"Josh William")
9. (items,"{""product"": ""Toy Car"",""qty"": 1}")
10. (customer,"Mary Clark")
11. (items,"{""product"": ""Toy Train"",""qty"": 2}")
12. (8 rows)

复制代码

其他示例

1. postgres=# SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');
2. key | value
3. -----+-------
4. a   | foo
5. b   | bar
6. (2 rows)
8. postgres=# SELECT json_each_text('{"a":"foo", "b":"bar"}');
9. json_each_text
10. ----------------
11. (a,foo)
12. (b,bar)
13. (2 rows)

复制代码

json_object_keys()

json_object_keys()函数可以获得json对象最外层的一组键。举例：

1. postgres=# SELECT json_object_keys (info->'items') FROM orders where id <=4;
2. json_object_keys
3. ------------------
4. product
5. qty
6. product
7. qty
8. product
9. qty
10. product
11. qty
12. (8 rows)

复制代码

json_typeof()

json_typeof函数返回json最外层key的数据类型作为字符串。可能是number, boolean, null, object, array, string。

1. -- 下面语句查询所有item的数据类型：
2. postgres=# SELECT json_typeof (info->'items') FROM orders where id <=4;
3. json_typeof
4. -------------
5. object
6. object
7. object
8. object
9. (4 rows)
11. -- 下面语句返回嵌套类型中qty字段的数据类型：
12. postgres=# SELECT json_typeof (info->'items'->'qty') FROM orders where id <=4;
13. json_typeof
14. -------------
15. number
16. number
17. number
18. number
19. (4 rows)

复制代码

json_object_field_text(json_obj json, key text)

在 PostgreSQL 中，json_object_field_text 函数用于从 JSON 对象中提取指定键的文本值。以下是其具体用法：
语法

1. json_object_field_text(json_obj json, key text) → text

复制代码

• json_obj: 目标 JSON 对象。
  
• key: 必要提取的字段名（键）。
  

功能
json_object_field_text 函数从 JSON 对象中提取指定键的值，并返回该值的文本表示。如果键不存在，则返回 NULL。
使用示例
假设有以下 JSON 数据：

1. { "customer": "Lily Bush", "items": {"product": "Diaper", "qty": 24} }

复制代码

1. 提取顶层字段值
从 info 字段中提取 customer 的值：

1. SELECT json_object_field_text(info, 'customer') AS customer_name
2. FROM orders;

复制代码

2. 提取嵌套字段值
对于嵌套字段，首先必要提取包含目标字段的对象，然后从中提取字段值。例如，提取 items 对象中的 product 值：

1. SELECT json_object_field_text(info, 'items') AS items_json
2. FROM orders;

复制代码

要进一步从 items JSON 对象中提取 product 字段，可以联合 jsonb 运算符：

1. SELECT (json_object_field_text(info, 'items')::jsonb->>'product') AS product_name
2. FROM orders;

复制代码

注意事项

• json_object_field_text 函数专用于提取 JSON 对象的顶层字段。对于嵌套字段，可能必要联合其他 JSON 操作函数和运算符。
  
• 如果使用 jsonb 类型，可以直接使用 -> 和 ->> 运算符来处理嵌套字段，而不必转换为 text。
  

示例表
假设 orders 表的 info 列为 JSON 类型，以下是如何使用这些函数的示例：

1. CREATE TABLE orders (
2.     id SERIAL PRIMARY KEY,
3.     info JSON
4. );
6. INSERT INTO orders (info) VALUES
7. ('{"customer": "Lily Bush", "items": {"product": "Diaper", "qty": 24}}'),
8. ('{"customer": "John Doe", "items": {"product": "Wipes", "qty": 50}}');

复制代码

查询 customer 字段的值：

1. SELECT json_object_field_text(info, 'customer') AS customer_name
2. FROM orders;

复制代码

提取 items 对象中的 product 值：

1. SELECT (json_object_field_text(info, 'items')::jsonb->>'product') AS product_name
2. FROM orders;

复制代码

json_extract_path_text(from_json json, VARIADIC path_elems text[])

以text返回由path_elems指向的 JSON 值（等效于#>>操作符）。

1. select json_extract_path_text(info,'customer') from orders where id<=4;
2. -- 等价于
3. select info #>> '{customer}' from orders where id<=4;
5.    ?column?
6. --------------
7. John Doe
8. Lily Bush
9. Josh William
10. Mary Clark
12. (5 rows)

复制代码

jsonb_pretty(from_json jsonb)

把*from_json*返回成一段 缩进后的 JSON 文本。

1. postgres=# select id,jsonb_pretty(info::jsonb) from orders where id=1;
2. id |        jsonb_pretty
3. ----+----------------------------
4.   1 | {                         +
5.     |     "items": {            +
6.     |         "qty": 6,         +
7.     |         "product": "Beer" +
8.     |     },                    +
9.     |     "customer": "John Doe"+
10.     | }
11. (1 row)

复制代码

jsonb_set

jsonb_set() 函数参数如下：

1. jsonb_set(target         jsonb,  // 需要修改的数据
2.           path           text[], // 数据路径
3.           new_value      jsonb,  // 新数据
4.           create_missing boolean default true)

复制代码

  如果create_missing 是true （缺省是true），并且path指定的路径在target 中不存在，那么target将包含path指定部门， new_value替换部门， 大概new_value添加部门。

1. -- 更新 target 第0 个元素 key 为 f1 的值，如果f1 不存在 忽略
2. select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
3.                   jsonb_set
4. ---------------------------------------------
5. [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
7. -- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 创建
8. select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');
9.                   jsonb_set
10. ---------------------------------------------
11. [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
13. -- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 忽略
14. select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]', false);
15.                   jsonb_set
16. ---------------------------------------------
17. [{"f1": 1, "f2": null}, 2]

复制代码

json_array_elements*

把顶层 JSON 数组扩展成一个text值聚集。

1. postgres=# select * from json_array_elements('[1,true,[2,false]]');
2.    value   
3. -----------
4. 1
5. true
6. [2,false]
7. (3 rows)
9. postgres=# select * from json_array_elements_text('["foo", "bar"]');
10. value
11. -------
12. foo
13. bar
14. (2 rows)

复制代码

json_array_length

返回顶层json数组中的元素数量。

1. postgres=# select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
2. json_array_length
3. -------------------
4.                  5
5. (1 row)
7. postgres=# select json_array_length('["foo", "bar"]');
8. json_array_length
9. -------------------
10.                  2
11. (1 row)

复制代码

JSON与JSONB读写性能测试

构建JSON、JSONB测试表

下面通过一个简朴的例子测试下json、jsonb的读写性能差异，计划创建以下三张表：

• user_ini：底子数据表，并插入200万测试数据；
  
• tbl_user_json:： json 数据类型表，200万数据；
  
• tbl_user_jsonb： jsonb 数据类型表，200万数据；
  

1. CREATE TABLE user_ini(
2.     id int4,
3.     user_id int8,
4.     user_name varchar(64),
5.     create_time timestamp(6) with time zone default clock_timestamp());
7. INSERT INTO user_ini(id,user_id,user_name)
8. SELECT r,round(random()*2000000), r || '_francs' FROM generate_series(1,2000000) as r;

复制代码

计划使用user_ini表数据生成json、jsonb数据，创建user_ini_json、user_ini_jsonb表，如下所示：

1. CREATE TABLE tbl_user_json(id serial, user_info json);
2. CREATE TABLE tbl_user_jsonb(id serial, user_info jsonb);

复制代码

JSON与JSONB表写性能测试

根据user_ini数据通过row_to_json函数向表user_ini_json插入200万json数据，如下：

1. postgres=# \timing
2. Timing is on.
3. postgres=# INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;
4. INSERT 0 2000000
5. Time: 15093.043 ms (00:15.093)

复制代码

从以上结果看出tbl_user_json插入200万数据花了15秒左右；接着根据user_ini表数据生成200万jsonb数据并插入表tbl_user_jsonb，如下：

1. postgres=# INSERT INTO tbl_user_jsonb(user_info) SELECT row_to_json(user_ini)::jsonb FROM user_ini;
2. INSERT 0 2000000
3. Time: 19801.533 ms (00:19.802)

复制代码

从以上看出tbl_user_jsonb表插入200万jsonb数据花了19秒左右，恰恰验证了json数据写入比jsonb快。
比力两表占用空间巨细，如下所示

1. postgres=# \dt+ tbl_user_json
2.                         List of relations
3. Schema |     Name      | Type  |  Owner   |  Size  | Description
4. --------+---------------+-------+----------+--------+-------------
5. public | tbl_user_json | table | postgres | 281 MB |
6. (1 row)
8. postgres=# \dt+ tbl_user_jsonb
9.                          List of relations
10. Schema |      Name      | Type  |  Owner   |  Size  | Description
11. --------+----------------+-------+----------+--------+-------------
12. public | tbl_user_jsonb | table | postgres | 333 MB |
13. (1 row)

复制代码

从占用空间来看，同样的数据量jsonb数据类型占用空间比json稍大。
随机查一条数据对比

1. postgres=# select * from tbl_user_json limit 1;
2. id |                                             user_info                                             
3. ----+----------------------------------------------------------------------------------------------------
4.   1 | {"id":1,"user_id":1141402,"user_name":"1_francs","create_time":"2024-08-11T01:33:57.532707+08:00"}
5. (1 row)
7. Time: 0.600 ms
8. postgres=# select * from tbl_user_jsonb limit 1;
9. id |                                                 user_info                                                
10. ----+-----------------------------------------------------------------------------------------------------------
11.   1 | {"id": 1, "user_id": 1141402, "user_name": "1_francs", "create_time": "2024-08-11T01:33:57.532707+08:00"}
12. (1 row)
14. Time: 0.710 ms

复制代码

JSON与JSONB表读性能测试

对于json、jsonb读性能测试我们选择基于json、jsonb键值查询的场景，例如，根据user_info字段的user_name键的值查询，如下所示：

1. postgres=# EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name'='1_francs';
2.                                                              QUERY PLAN                                                            
3. ------------------------------------------------------------------------------------------------------------------------------------
4. Gather  (cost=1000.00..57053.22 rows=10000 width=143) (actual time=0.576..213.625 rows=1 loops=1)
5.    Workers Planned: 2
6.    Workers Launched: 2
7.    ->  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)
8.          Filter: ((user_info ->> 'user_name'::text) = '1_francs'::text)
9.          Rows Removed by Filter: 666666
10. Planning Time: 0.098 ms
11. Execution Time: 213.656 ms
12. (8 rows)
15. postgres=# EXPLAIN ANALYZE SELECT * FROM tbl_user_json WHERE user_info->>'user_name'='1_francs';
16.                                                             QUERY PLAN                                                            
17. -----------------------------------------------------------------------------------------------------------------------------------
18. Gather  (cost=1000.00..50401.43 rows=10000 width=113) (actual time=0.353..746.473 rows=1 loops=1)
19.    Workers Planned: 2
20.    Workers Launched: 2
21.    ->  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)
22.          Filter: ((user_info ->> 'user_name'::text) = '1_francs'::text)
23.          Rows Removed by Filter: 666666
24. Planning Time: 0.066 ms
25. Execution Time: 746.494 ms
26. (8 rows)

复制代码

如上普通查询没有走索引的环境可以看出jsonb的读更快一点。
为更好的对比tbl_user_json、tbl_user_jsonb表基于键值查询的效率，计划根据user_info字段id键举行范围扫描对比性能，创建索引如下：

1. CREATE INDEX idx_gin_user_info_id ON tbl_user_json USING btree (((user_info ->> 'id')::integer));
2. CREATE INDEX idx_gin_user_infob_id ON tbl_user_jsonb USING btree (((user_info ->> 'id')::integer));

复制代码

创建索引后对比查询性能

1. EXPLAIN ANALYZE SELECT id,user_info->'id',user_info->'user_name' FROM tbl_user_json
2. WHERE (user_info->>'id')::int4>1 AND (user_info->>'id')::int4<10000;
3.                                                               QUERY PLAN                                                              
4. --------------------------------------------------------------------------------------------------------------------------------------
5. 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)
6.    Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
7.    Heap Blocks: exact=173
8.    ->  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)
9.          Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
10. Planning Time: 0.546 ms
11. Execution Time: 25.101 ms
12. (7 rows)
15. EXPLAIN ANALYZE SELECT id,user_info->'id',user_info->'user_name' FROM tbl_user_jsonb
16. WHERE (user_info->>'id')::int4>1 AND (user_info->>'id')::int4<10000;
17.                                                               QUERY PLAN                                                               
18. ---------------------------------------------------------------------------------------------------------------------------------------
19. 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)
20.    Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
21.    Heap Blocks: exact=212
22.    ->  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)
23.          Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
24. Planning Time: 0.276 ms
25. Execution Time: 7.413 ms
26. (7 rows)

复制代码

如上user_info字段id键值在1到10000范围内的记录走了索引，而且jsonb检索效率比json快了四倍以上。
从以上两个测试看出，恰恰验证了 “json写入比jsonb快，但检索时比jsonb慢” 的观点，值得一提的是如果必要通过key/value举行检索，例如以下。

1. EXPLAIN ANALYZE SELECT * FROM tbl_user_json WHERE user_info::jsonb @> '{"user_name": "2_francs"}';
2.                                                             QUERY PLAN                                                            
3. -----------------------------------------------------------------------------------------------------------------------------------
4. Gather  (cost=1000.00..51684.33 rows=2000 width=113) (actual time=0.355..1144.857 rows=1 loops=1)
5.    Workers Planned: 2
6.    Workers Launched: 2
7.    ->  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)
8.          Filter: ((user_info)::jsonb @> '{"user_name": "2_francs"}'::jsonb)
9.          Rows Removed by Filter: 666666
10. Planning Time: 0.197 ms
11. Execution Time: 1144.885 ms
12. (8 rows)
16. EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_francs"}';
17.                                                             QUERY PLAN                                                            
18. -----------------------------------------------------------------------------------------------------------------------------------
19. Gather  (cost=1000.00..54169.67 rows=2000 width=143) (actual time=0.473..233.123 rows=1 loops=1)
20.    Workers Planned: 2
21.    Workers Launched: 2
22.    ->  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)
23.          Filter: (user_info @> '{"user_name": "2_francs"}'::jsonb)
24.          Rows Removed by Filter: 666666
25. Planning Time: 0.114 ms
26. Execution Time: 233.147 ms
27. (8 rows)
29. Time: 233.684 ms

复制代码

在tbl_user_jsonb字段user_info上创建gin索引，如下所示：

1. CREATE INDEX idx_tbl_user_jsonb_user_Info ON tbl_user_jsonb USING gin(user_Info);

复制代码

索引创建后，再次执行一下，如下所示：

1. EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_francs"}';
2.                                                                QUERY PLAN                                                               
3. -----------------------------------------------------------------------------------------------------------------------------------------
4. 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)
5.    Recheck Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)
6.    Heap Blocks: exact=1
7.    ->  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)
8.          Index Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)
9. Planning Time: 0.632 ms
10. Execution Time: 0.228 ms
11. (7 rows)

复制代码

PostgreSQ全文检索支持JSON和JSONB

PostgreSQL全文检索简介

对于大多数应用全文检索很少放到数据库中实现，一样平常使用单独的全文检索引擎，例如基于SQL全文检索引擎Sphinx。PostgreSQL支持全文检索，对于规模不大的应用如果不想搭建专门的搜索引擎，PostgreSQL的全文检索也可以满足需求。
如果没有使用专门的搜索引擎，大部检索必要通过数据库like操作匹配，这种检索方式重要缺点在于：

• 不能很好的支持索引，通常需全表扫描检索数据，数据量大时检索性能很低。
  
• 不提供检索结果排序，当输出结果数据量非常大时表现更加显着。
  

PostgreSQL全文检索能有用地办理这个问题，PostgreSQL全文检索通过以下两种数据类型来实现。
1、tsvector

tsvector全文检索数据类型代表一个被优化的可以基于搜索的文档，将一串字符串转换成tsvector全文检索数据类型，如下：

1. postgres=# SELECT 'Hello,cat,how are u? cat is smiling! '::tsvector;
2.                      tsvector                     
3. --------------------------------------------------
4. 'Hello,cat,how' 'are' 'cat' 'is' 'smiling!' 'u?'
5. (1 row)

复制代码

可以看到，字符串的内容被分隔成好几段，但通过::tsvector只是做类型转换，没有举行数据标准化处理，对于英文全文检索可通过函数to_tsvector举行数据标准化，如下所示：

1. postgres=# SELECT to_tsvector('english','Hello cat,');
2.     to_tsvector   
3. -------------------
4. 'cat':2 'hello':1
5. (1 row)

复制代码

2、tsquery

tsquery表示一个文本查询，存储用于搜索的词，并且支持布尔操作&、|、!，将字符串转换成tsquery，如下所示：

1. postgres=# SELECT 'hello&cat'::tsquery;
2.      tsquery     
3. -----------------
4. 'hello' & 'cat'
5. (1 row)

复制代码

上述只是转换成tsquery类型，而并没有做标准化，使用to_tsquery函数可以执行标准化，如下所示：

1. postgres=# SELECT to_tsquery('hello&cat');
2.    to_tsquery   
3. -----------------
4. 'hello' & 'cat'
5. (1 row)

复制代码

一个全文检索示例如下，检索字符串是否包罗hello和cat字符，本例中返回真。

1. postgres=# SELECT to_tsvector('english','Hello cat,how are u') @@ to_tsquery('hello&cat');
2. ?column?
3. ----------
4. t
5. (1 row)

复制代码

检索字符串是否包含字符hello和dog，本例中返回假。

1. postgres=# SELECT to_tsvector('english','Hello cat,how are u') @@ to_tsquery('hello&dog');
2. ?column?
3. ----------
4. f
5. (1 row)

复制代码

有爱好可以测试一下tsquery的其他操作符，例如|、!等。
注意：这里使用了带双参数的to_tsvector函数，函数to_tsvector双参数的格式如下：

1. to_tsvector([ config regconfig , ] document text)
2. #本节to_tsvector函数指定了config参数为english，如果不指定config参数，则默认使用default_text_search_config参数的配置。
5. postgres=# select name,setting,unit,context from pg_settings where name ~* 'default_text_search_config';
6.             name            |      setting       | unit | context
7. ----------------------------+--------------------+------+---------
8. default_text_search_config | pg_catalog.english |      | user
9. (1 row)

复制代码

英文全文检索例子

下面演示一个英文全文检索示例，创建一张测试表并插入200万测试数据，如下所示：

1. CREATE TABLE test_search(id int4,name text);
2. INSERT INTO test_search(id,name) SELECT n, n||'_francs' FROM generate_series(1,2000000) n;

复制代码

执行以下SQL，查询test_search表name字段包含字符1_francs的记录。

1. postgres=# SELECT * FROM test_search WHERE name LIKE '1_francs';
2. id |   name   
3. ----+----------
4.   1 | 1_francs
5. (1 row)
7. -- 执行计划
8. EXPLAIN ANALYZE SELECT * FROM test_search WHERE name LIKE '1_francs';
9.                                                          QUERY PLAN                                                         
10. ----------------------------------------------------------------------------------------------------------------------------
11. Gather  (cost=1000.00..24166.67 rows=200 width=18) (actual time=0.423..84.066 rows=1 loops=1)
12.    Workers Planned: 2
13.    Workers Launched: 2
14.    ->  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)
15.          Filter: (name ~~ '1_francs'::text)
16.          Rows Removed by Filter: 666666
17. Planning Time: 0.262 ms
18. Execution Time: 84.090 ms
19. (8 rows)

复制代码

以上执行计划走了全表扫描，执行时间为84毫秒左右，性能很低，接着创建索引，如下所示

1. CREATE INDEX idx_gin_search ON test_search USING gin(to_tsvector('english',name));

复制代码

执行以下SQL，查询test_search表name字段包含字符1_francs的记录。

1. postgres=# SELECT * FROM test_search WHERE to_tsvector('english',name) @@ to_tsquery('english','1_francs');
2. id |   name   
3. ----+----------
4.   1 | 1_francs
5. (1 row)
8. -- 执行计划
9. EXPLAIN ANALYZE SELECT * FROM test_search WHERE to_tsvector('english',name) @@ to_tsquery('english','1_francs');
10.                                                        QUERY PLAN                                                        
11. -------------------------------------------------------------------------------------------------------------------------
12. 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)
13.    Recheck Cond: (to_tsvector('english'::regconfig, name) @@ '''1'' & ''franc'''::tsquery)
14.    Heap Blocks: exact=1
15.    ->  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)
16.          Index Cond: (to_tsvector('english'::regconfig, name) @@ '''1'' & ''franc'''::tsquery)
17. Planning Time: 0.289 ms
18. Execution Time: 0.157 ms
19. (7 rows)

复制代码

创建索引后，以上查询走了索引并且执行时间下降到0.157毫秒，性能提拔很大，值得一提的是如果SQL改成以下，则不走索引，如下所示：

1. EXPLAIN ANALYZE SELECT * FROM test_search WHERE to_tsvector(name) @@ to_tsquery('1_francs');
2.                                                            QUERY PLAN                                                            
3. ---------------------------------------------------------------------------------------------------------------------------------
4. Gather  (cost=1000.00..440818.33 rows=50 width=18) (actual time=0.339..2869.023 rows=1 loops=1)
5.    Workers Planned: 2
6.    Workers Launched: 2
7.    ->  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)
8.          Filter: (to_tsvector(name) @@ to_tsquery('1_francs'::text))
9.          Rows Removed by Filter: 666666
10. Planning Time: 0.160 ms
11. Execution Time: 2869.147 ms
12. (8 rows)

复制代码

由于创建索引时使用的是to_tsvector('english',name)函数索引，带了两个参数，因此where条件中的to_tsvector函数带两个参数才能走索引，而to_tsvector(name)不走索引。
JSON、JSONB全文检索实践

在PostgreSQL10版本之前全文检索不支持json和jsonb数据类型，10版本的一个重要特性是全文检索支持json和jsonb数据类型，这一末节演示下10版本以后的这个新特性。
当前测试版本为12版本

1. postgres=# select version();
2.                                                  version                                                  
3. ----------------------------------------------------------------------------------------------------------
4. 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
5. (1 row)

复制代码

PostgreSQL12版本与9.6版本to_tsvector函数的差异

先来看下9.6版本to_tsvector函数，如下：

1. [pg96@pghost1 ~]$ psql
2. psql (9.6.21)
3. Type "help" for help.
5. postgres=# \df *to_tsvector*
6.                                 List of functions
7.    Schema   |       Name        | Result data type | Argument data types |  Type  
8. ------------+-------------------+------------------+---------------------+--------
9. pg_catalog | array_to_tsvector | tsvector         | text[]              | normal
10. pg_catalog | to_tsvector       | tsvector         | regconfig, text     | normal
11. pg_catalog | to_tsvector       | tsvector         | text                | normal
12. (3 rows)

复制代码

从以上看出9.6版本to_tsvector函数的输入参数仅支持text、text[]数据类型，接着看下12版本的to_tsvector函数，如下所示：

1. [postgres@centos7 ~]$ psql
2. psql (12.17)
3. Type "help" for help.
5. postgres=# \df *to_tsvector*
6.                                  List of functions
7.    Schema   |       Name        | Result data type |   Argument data types   | Type
8. ------------+-------------------+------------------+-------------------------+------
9. pg_catalog | array_to_tsvector | tsvector         | text[]                  | func
10. pg_catalog | json_to_tsvector  | tsvector         | json, jsonb             | func
11. pg_catalog | json_to_tsvector  | tsvector         | regconfig, json, jsonb  | func
12. pg_catalog | jsonb_to_tsvector | tsvector         | jsonb, jsonb            | func
13. pg_catalog | jsonb_to_tsvector | tsvector         | regconfig, jsonb, jsonb | func
14. pg_catalog | to_tsvector       | tsvector         | json                    | func
15. pg_catalog | to_tsvector       | tsvector         | jsonb                   | func
16. pg_catalog | to_tsvector       | tsvector         | regconfig, json         | func
17. pg_catalog | to_tsvector       | tsvector         | regconfig, jsonb        | func
18. pg_catalog | to_tsvector       | tsvector         | regconfig, text         | func
19. pg_catalog | to_tsvector       | tsvector         | text                    | func
20. (11 rows)

复制代码

从以上看出，12版本的to_tsvector函数支持的数据类型增加了json和jsonb。
JSON数据全文检索测试

创建数据生成函数
为了便于生成测试数据，创建以下两个函数用来随机生成指定长度的字符串，创建random_range(int4, int4)函数如下：

1. CREATE OR REPLACE FUNCTION random_range(int4, int4)
2. RETURNS int4
3. LANGUAGE SQL
4. AS $$
5.     SELECT ($1 + FLOOR(($2 - $1 + 1) * random() ))::int4;
6. $$;
8. -- 接着创建random_text_simple(length int4)函数，此函数会调用random_range(int4, int4)函数。
9. CREATE OR REPLACE FUNCTION random_text_simple(length int4)
10. RETURNS text
11. LANGUAGE PLPGSQL
12. AS $$
13. DECLARE
14.     possible_chars text := '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
15.     output text := '';
16.     i int4;
17.     pos int4;
18. BEGIN
19.     FOR i IN 1..length LOOP
20.         pos := random_range(1, length(possible_chars));
21.         output := output || substr(possible_chars, pos, 1);
22.     END LOOP;
23.     RETURN output;
24. END;
25. $$;

复制代码

random_text_simple(length int4)函数可以随机生成指定长度字符串，如下示例：

1. postgres=# SELECT random_text_simple(3);
2. random_text_simple
3. --------------------
4. 4dI
5. (1 row)
7. postgres=# SELECT random_text_simple(6);
8. random_text_simple
9. --------------------
10. 3uLknl
11. (1 row)

复制代码

创建JSON测试表
创建user_ini测试表，并通过random_text_simple(length int4)函数插入100万随机生成六位字符的字符串测试数据，如下所示：

1. drop table if exists user_ini;
2. CREATE TABLE user_ini(
3.     id int4,
4.     user_id int8,
5.     user_name varchar(64),
6.     create_time timestamp(6) with time zone default clock_timestamp());
8. INSERT INTO user_ini(id,user_id,user_name)
9. 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数据，如下所示：

1. CREATE TABLE tbl_user_search_json(id serial, user_info json);
2. INSERT INTO tbl_user_search_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;
4. -- 生成的数据如下：
5. postgres=# SELECT * FROM tbl_user_search_json LIMIT 1;
6. id |                                           user_info                                            
7. ----+------------------------------------------------------------------------------------------------
8.   1 | {"id":1,"user_id":185716,"user_name":"mOuXBE","create_time":"2024-08-11T03:27:46.69352+08:00"}
9. (1 row)

复制代码

使用全文检索查询表tbl_user_search_json的user_info字段中包含KTU89H字符的记录，如下所示：

1. postgres=# SELECT * FROM tbl_user_search_json WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','bb7tQk');
2. id  |                                             user_info                                             
3. -----+---------------------------------------------------------------------------------------------------
4. 100 | {"id":100,"user_id":640314,"user_name":"bb7tQk","create_time":"2024-08-11T03:27:46.694603+08:00"}
5. (1 row)
7. -- 执行计划
8. EXPLAIN ANALYZE SELECT * FROM tbl_user_search_json WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','bb7tQk');
9.                                                                  QUERY PLAN                                                                  
10. ---------------------------------------------------------------------------------------------------------------------------------------------
11. Gather  (cost=1000.00..127886.00 rows=5000 width=104) (actual time=1.415..2299.886 rows=1 loops=1)
12.    Workers Planned: 2
13.    Workers Launched: 2
14.    ->  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)
15.          Filter: (to_tsvector('english'::regconfig, user_info) @@ '''bb7tqk'''::tsquery)
16.          Rows Removed by Filter: 333333
17. Planning Time: 0.091 ms
18. Execution Time: 2299.910 ms
19. (8 rows)

复制代码

以上SQL能正常执行说明全文检索支持json数据类型，只是上述SQL走了全表扫描性能低，执行时间为2299毫秒
创建索引

1. CREATE INDEX idx_gin_search_json ON tbl_user_search_json USING gin(to_tsvector('english',user_info));

复制代码

索引创建后，再次执行以下SQL，如下所示：

1. EXPLAIN ANALYZE SELECT * FROM tbl_user_search_json WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','bb7tQk');
2.                                                            QUERY PLAN                                                           
3. --------------------------------------------------------------------------------------------------------------------------------
4. 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)
5.    Recheck Cond: (to_tsvector('english'::regconfig, user_info) @@ '''bb7tqk'''::tsquery)
6.    Heap Blocks: exact=1
7.    ->  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)
8.          Index Cond: (to_tsvector('english'::regconfig, user_info) @@ '''bb7tqk'''::tsquery)
9. Planning Time: 0.439 ms
10. Execution Time: 0.079 ms
11. (7 rows)

复制代码

从上述执行计划看出走了索引，并且执行时间降为0.079毫秒，性能非常不错。
索引支持

jsonb缺省的GIN操作符类支持使用@>、?、?&和?|操作符查询
JSONB类型支持GIN, BTree索引。一样平常环境下，我们会在JSONB类型字段上建GIN索引，语法如下：

1. CREATE INDEX idx_name ON table_name USING gin (idx_col);
2. 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 函数索引

1. CREATE TABLE test_json (json_type text,obj json);
2. insert into test_json values
3. ('aa', '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}'),
4. ('cc', '{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');
7. select obj->'f2' from test_json where json_type = 'aa';
8. ?column?
9. ----------
10. {"f3":1}
11. (1 row)
13. -- 创建函数索引
14. create index i on test_json (json_extract_path_text(obj, 'f4'));
17. select * from test_json where json_extract_path_text(obj, 'f4') = '{"f5":99,"f6":"foo"}';
18. json_type |                 obj
19. -----------+-------------------------------------------
20. aa        | {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}
21. (1 row)

复制代码

JSONB创建索引

1. -- 创建测试表并生成数据
2. CREATE TABLE test_t (id int,info json);
4. -- 创建随机生成字符串函数
5. create or replace function random_string(integer)
6. returns text as
7. $body$
8.     select array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), '');
9. $body$
10. language sql volatile;
12. -- 插入数据
13. insert into test_t
14. select t.seq, ('{"a":{"a1":"a1a1", "a2":"a2a2"}, "name":"'||random_string(10)||'","b":"bbbbb"}')::json
15. from generate_series(1, 10000000) as t(seq);
17. CREATE TABLE test_t2 (id int,info jsonb);
18. CREATE TABLE test_t3 (id int,info jsonb);
19. insert into test_t2 select id, info::jsonb from test_t;
20. insert into test_t3 select id, info::jsonb from test_t;
22. -- 建立索引
23. CREATE INDEX idx_test2 ON test_t2 USING gin(info);
24. CREATE INDEX idx_test3 ON test_t3 USING gin(info jsonb_path_ops);
26. -- 未建索引执行
27. EXPLAIN ANALYZE SELECT * FROM test_t where info::jsonb @> '{"name":"FMYYFF6I5O"}';
28.                                                           QUERY PLAN
29. -------------------------------------------------------------------------------------------------------------------------------
30. Gather  (cost=1000.00..207373.79 rows=100000 width=70) (actual time=2.398..6762.787 rows=1 loops=1)
31.    Workers Planned: 2
32.    Workers Launched: 2
33.    ->  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)
34.          Filter: ((info)::jsonb @> '{"name": "FMYYFF6I5O"}'::jsonb)
35.          Rows Removed by Filter: 3333333
36. Planning Time: 0.069 ms
37. Execution Time: 6762.834 ms
38. (8 rows)
40. -- 使用jsonb_ops操作符创建索引执行
41. EXPLAIN ANALYZE SELECT * FROM test_t2 where info @> '{"name":"FMYYFF6I5O"}';
42.                                                       QUERY PLAN
43. ----------------------------------------------------------------------------------------------------------------------
44. 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)
45.    Recheck Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
46.    Heap Blocks: exact=1
47.    ->  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)
48.          Index Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
49. Planning Time: 3.766 ms
50. Execution Time: 0.635 ms
51. (7 rows)
53. -- 使用jsonb_path_ops操作符创建索引执行
54. postgres=# EXPLAIN ANALYZE SELECT * FROM test_t3 where info @> '{"name":"FMYYFF6I5O"}';
55.                                                       QUERY PLAN
56. ----------------------------------------------------------------------------------------------------------------------
57. 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)
58.    Recheck Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
59.    Heap Blocks: exact=1
60.    ->  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)
61.          Index Cond: (info @> '{"name": "FMYYFF6I5O"}'::jsonb)
62. Planning Time: 0.366 ms
63. Execution Time: 0.437 ms
64. (7 rows)

复制代码

JSONB性能分析

数据预备

1. -- account 表 id 使用uuid 类型，需要先添加uuid-ossp模块。
2. CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
4. -- create table
5. 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);
6. postgres=> \d account
7.                                Table "public.account"
8.     Column    |           Type           | Collation | Nullable |      Default
9. --------------+--------------------------+-----------+----------+--------------------
10. id           | uuid                     |           | not null |uuid_generate_v1()
11. content      | jsonb                    |           |          |
12. created_at   | timestamp with time zone |           |          | CURRENT_TIMESTAMP
13. updated_at   | timestamp with time zone |           |          | CURRENT_TIMESTAMP
14. Indexes:
15.     "account_pkey" PRIMARY KEY, btree (id)

复制代码

content 数据布局为

1. content = {
2.     "nickname": {"type": "string"},
3.     "avatar": {"type": "string"},
4.     "weixin": {"type": "string"},
5.     "tags": {"type": "array", "items": {"type": "string"}},
6. }

复制代码

批量插入数据

1. -- 插入100w条有 nickname avatar tags 为["python", "golang", "c"]的数据
2. 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;
4. -- 插入100w条有 nickname tags 为["python", "golang"]的数据
5. 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;
7. -- 插入100w条有 nickname tags 为["python"]的数据
8. 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(*) 查询不是一个好的测试语句，就算是有索引，也只能起到过滤的作用，如果结果集比力大，查询速度还是会很慢

1. explain analyze select count(*) from account where content::jsonb ? 'avatar';
2.                                                                 QUERY PLAN
3. ------------------------------------------------------------------------------------------------------------------------------------------
4. Finalize Aggregate  (cost=45210.65..45210.66 rows=1 width=8) (actual time=148.609..159.876 rows=1 loops=1)
5.    ->  Gather  (cost=45210.43..45210.64 rows=2 width=8) (actual time=148.390..159.864 rows=3 loops=1)
6.          Workers Planned: 2
7.          Workers Launched: 2
8.          ->  Partial Aggregate  (cost=44210.43..44210.44 rows=1 width=8) (actual time=136.871..136.872 rows=1 loops=3)
9.                ->  Parallel Seq Scan on account  (cost=0.00..44111.50 rows=39573 width=0) (actual time=0.038..134.372 rows=33333 loops=3)
10.                      Filter: (content ? 'avatar'::text)
11.                      Rows Removed by Filter: 666667
12. Planning Time: 1.671 ms
13. Execution Time: 159.937 ms
14. (10 rows)

复制代码

content 中没有avatar key 的数据条数

1. explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;
2.                                                                  QUERY PLAN
3. --------------------------------------------------------------------------------------------------------------------------------------------
4. Finalize Aggregate  (cost=47200.28..47200.29 rows=1 width=8) (actual time=206.111..217.322 rows=1 loops=1)
5.    ->  Gather  (cost=47200.07..47200.28 rows=2 width=8) (actual time=205.930..217.311 rows=3 loops=1)
6.          Workers Planned: 2
7.          Workers Launched: 2
8.          ->  Partial Aggregate  (cost=46200.07..46200.08 rows=1 width=8) (actual time=201.030..201.031 rows=1 loops=3)
9.                ->  Parallel Seq Scan on account  (cost=0.00..44111.50 rows=835427 width=0) (actual time=0.030..157.985 rows=666667 loops=3)
10.                      Filter: (NOT (content ? 'avatar'::text))
11.                      Rows Removed by Filter: 33333
12. Planning Time: 0.112 ms
13. Execution Time: 217.376 ms
14. (10 rows)

复制代码

查询content 中nickname 为nn-194318的数据

1. explain analyze select * from account where content @> '{"nickname": "nn-194318"}';
2.                                                         QUERY PLAN
3. --------------------------------------------------------------------------------------------------------------------------
4. Gather  (cost=1000.00..45132.50 rows=210 width=95) (actual time=197.300..208.413 rows=1 loops=1)
5.    Workers Planned: 2
6.    Workers Launched: 2
7.    ->  Parallel Seq Scan on account  (cost=0.00..44111.50 rows=88 width=95) (actual time=133.899..192.681 rows=0 loops=3)
8.          Filter: (content @> '{"nickname": "nn-194318"}'::jsonb)
9.          Rows Removed by Filter: 700000
10. Planning Time: 0.206 ms
11. Execution Time: 208.447 ms
12. (8 rows)

复制代码

对应的查询id 为 ‘a830f154-5711-11ef-aac7-000c29d4de9c’ 的数据

1. explain analyze select * from account where id='a830f154-5711-11ef-aac7-000c29d4de9c';
2.                                                       QUERY PLAN
3. -----------------------------------------------------------------------------------------------------------------------
4. 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)
5.    Index Cond: (id = 'a830f154-5711-11ef-aac7-000c29d4de9c'::uuid)
6. Planning Time: 0.089 ms
7. Execution Time: 0.059 ms
8. (4 rows)

复制代码

通过结果可以看到 使用 jsonb 查询和使用主键查询速度差异巨大，通过看查询分析记录可以看到，这两个语句最大的差异在于使用主键的查询用到了索引，而content nickname 的查询没有索引可以使用。 接下来测试一下使用索引时的查询速度。
索引

JSONB 最常用的是GIN 索引，GIN 索引可以被用来有用地搜索在大量jsonb文档（数据）中出现 的键大概键值对。
jsonb的默认 GIN 操作符类支持使用顶层键存在运算符?、?&以及?| 操作符和路径/值存在运算符@>的查询。
创建默认索引

1. CREATE INDEX idxgin ON account USING GIN (content);
3. 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只支持索引@>操作符。

1. -- 创建指定路径的索引
2. CREATE INDEX idxginp ON account USING GIN (content jsonb_path_ops);

复制代码

查询优化

创建默认索引

1. -- 创建简单索引
2. create index ix_account_content on account USING GIN (content);

复制代码

现在下面这样的查询就能使用该索引：

1. -- content 中有avatar key 的数据条数
2. explain analyze select count(*) from account where content::jsonb ? 'avatar';
3.                                                                  QUERY PLAN
4. --------------------------------------------------------------------------------------------------------------------------------------------
5. Aggregate  (cost=35490.68..35490.69 rows=1 width=8) (actual time=42.619..42.621 rows=1 loops=1)
6.    ->  Bitmap Heap Scan on account  (cost=892.06..35253.24 rows=94975 width=0) (actual time=8.532..36.016 rows=100000 loops=1)
7.          Recheck Cond: (content ? 'avatar'::text)
8.          Heap Blocks: exact=2041
9.          ->  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)
10.                Index Cond: (content ? 'avatar'::text)
11. Planning Time: 0.495 ms
12. Execution Time: 42.752 ms
13. (8 rows)

复制代码

和之前没有添加索引时速度提拔了3倍。

1. -- 查询content 中nickname 为nn-194318的数据
2. explain analyze select * from account where content@>'{"nickname": "nn-194318"}';
3.                                                           QUERY PLAN
4. ------------------------------------------------------------------------------------------------------------------------------
5. Bitmap Heap Scan on account  (cost=37.63..830.13 rows=210 width=95) (actual time=0.109..0.110 rows=1 loops=1)
6.    Recheck Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)
7.    Heap Blocks: exact=1
8.    ->  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)
9.          Index Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)
10. Planning Time: 0.140 ms
11. Execution Time: 0.147 ms
12. (7 rows)

复制代码

这个查询效率提拔更显着，竟然比使用主键还要高效。
但是下面这种查询并不能使用索引：

1. -- 查询content 中不存在 avatar key 的数据条数
2. explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;
3.                                                                  QUERY PLAN
4. --------------------------------------------------------------------------------------------------------------------------------------------
5. Finalize Aggregate  (cost=47200.28..47200.29 rows=1 width=8) (actual time=221.257..232.061 rows=1 loops=1)
6.    ->  Gather  (cost=47200.07..47200.28 rows=2 width=8) (actual time=221.052..232.049 rows=3 loops=1)
7.          Workers Planned: 2
8.          Workers Launched: 2
9.          ->  Partial Aggregate  (cost=46200.07..46200.08 rows=1 width=8) (actual time=215.582..215.583 rows=1 loops=3)
10.                ->  Parallel Seq Scan on account  (cost=0.00..44111.50 rows=835427 width=0) (actual time=0.033..170.202 rows=666667 loops=3)
11.                      Filter: (NOT (content ? 'avatar'::text))
12.                      Rows Removed by Filter: 33333
13. Planning Time: 0.120 ms
14. Execution Time: 232.136 ms
15. (10 rows)

复制代码

该索引也不能被用于下面这样的查询，因为只管操作符? 是可索引的，但它不能直接被应用于被索引列content：

1. explain analyze select count(1) from account where content -> 'tags' ? 'c';
2.                                                                QUERY PLAN
3. -----------------------------------------------------------------------------------------------------------------------------------------
4. Finalize Aggregate  (cost=47321.09..47321.10 rows=1 width=8) (actual time=185.699..196.680 rows=1 loops=1)
5.    ->  Gather  (cost=47320.88..47321.08 rows=2 width=8) (actual time=185.500..196.670 rows=3 loops=1)
6.          Workers Planned: 2
7.          Workers Launched: 2
8.          ->  Partial Aggregate  (cost=46320.88..46320.89 rows=1 width=8) (actual time=180.745..180.747 rows=1 loops=3)
9.                ->  Parallel Seq Scan on account  (cost=0.00..46299.00 rows=8750 width=0) (actual time=0.030..178.474 rows=33333 loops=3)
10.                      Filter: ((content -> 'tags'::text) ? 'c'::text)
11.                      Rows Removed by Filter: 666667
12. Planning Time: 0.108 ms
13. Execution Time: 196.751 ms
14. (10 rows)

复制代码

使用表达式索引

1. -- 创建路径索引
2. create index ix_account_content_tags on account USING GIN ((content->'tags'));
4. -- 测试查询性能
5. explain analyze select count(1) from account where content -> 'tags' ? 'c';
6.                                                                    QUERY PLAN
7. -------------------------------------------------------------------------------------------------------------------------------------------------
8. Aggregate  (cost=31745.00..31745.01 rows=1 width=8) (actual time=49.878..49.880 rows=1 loops=1)
9.    ->  Bitmap Heap Scan on account  (cost=754.75..31692.50 rows=21000 width=0) (actual time=8.473..43.123 rows=100000 loops=1)
10.          Recheck Cond: ((content -> 'tags'::text) ? 'c'::text)
11.          Heap Blocks: exact=2041
12.          ->  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)
13.                Index Cond: ((content -> 'tags'::text) ? 'c'::text)
14. Planning Time: 0.251 ms
15. Execution Time: 49.934 ms
16. (8 rows)

复制代码

现在，WHERE 子句content -> 'tags' ? 'c' 将被辨认为可索引操作符?在索引表达式content -> 'tags' 上的应用。
也可以使用包含查询的方式，例如：

1. -- 查寻 "tags" 包含数组元素 "c" 的数据的个数
2. explain analyze select count(1) from account where content @> '{"tags": ["c"]}';
3.                                                                   QUERY PLAN
4. ----------------------------------------------------------------------------------------------------------------------------------------------
5. Aggregate  (cost=35506.68..35506.69 rows=1 width=8) (actual time=68.712..68.714 rows=1 loops=1)
6.    ->  Bitmap Heap Scan on account  (cost=908.06..35269.24 rows=94975 width=0) (actual time=11.153..61.405 rows=100000 loops=1)
7.          Recheck Cond: (content @> '{"tags": ["c"]}'::jsonb)
8.          Heap Blocks: exact=2041
9.          ->  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)
10.                Index Cond: (content @> '{"tags": ["c"]}'::jsonb)
11. Planning Time: 0.257 ms
12. Execution Time: 68.777 ms
13. (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的任意一个

1. -- 存在某个KEY(TOP LEVEL)
2. '{"a":1, "b":2}'::jsonb ? 'b'
4. -- 存在所有KEY
5. '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'c']
7. -- 存在任意key、元素
8. '["a", "b"]'::jsonb ?| array['a', 'b']  

复制代码

2、等值，JSON中是否存在指定的key:value对（支持嵌套JSON）

1. '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

复制代码

3、包含，JSON中某个路径下的VALUE（数组）中，是否包含指定的所有元素。

1. postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';
2. ?column?
3. ----------
4. t
5. (1 row)

复制代码

4、相交，JSON中某个路径下的VALUE（数组）中，是否包含指定的任意元素。

1. 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]}}';
2. ?column?
3. ----------
4. t
5. (1 row)

复制代码

或(注意1,2,3必要双引号，作为text类型存储，因为操作符?| ?&临时只支持了text[]，如果是numeric匹配不上)

1. postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?& array['2','3','4'];
2. ?column?
3. ----------
4. f
5. (1 row)
7. postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?| array['2','3','4'];
8. ?column?
9. ----------
10. t
11. (1 row)

复制代码

5、范围查找，JSON中某个路径下的VALUE，是否落在某个范围内。

1. (js ->> 'key1' )::numeric between xx and xx
2.   
3. (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只支持 @> 操作符，但是效率高）

1. postgres=# create table tbl(id int, js jsonb);
2. CREATE TABLE  
3. postgres=# create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);
4. CREATE INDEX

复制代码

2、支持除范围查询以外的所有查询的索引如下

1. postgres=# create table tbl(id int, js jsonb);
2. CREATE TABLE  
3. postgres=# create index idx_tbl_1 on tbl using gin (js);  -- 使用默认ops即可  
4. CREATE INDEX

复制代码

二、JSON KEY VALUE值范围查询加速

某些使用，必要对VALUE使用范围查询，比如时间(如果要建索引，请使用numeric表示，否则必要自定义immutable函数)，数值都有这些需求。
通常的做法，把范围查询的类型提取出来，创建btree表达式索引，如果有任意组合的范围查询，使用gin或rum表达式索引。
例子

1. create index idx1 on tbl ( ((js->>'k1')::float8) );  
2. create index idx2 on tbl ( ((js->>'k2')::numeric) );  
3. ...  
4. create index idxn on tbl ( ((js->>'kn')::float8) );

复制代码

或

1. create extension btree_gin;
2. create index idx1 on tbl using gin( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );

复制代码

或

1. create extension rum;
2. create index idx1 on tbl using rum( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );

复制代码

或

1. create or replace function to_timestamp(text) returns timestamp as $$  
2.   select $1::timestamp;  
3. $$ language sql strict immutable;  
4.   
5.   
6. create index idx1 on tbl using gin( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );  
7. -- 或  
8. create index idx1 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );

复制代码

三、索引使用例子

rum插件说明

1. #代码仓
2. https://github.com/postgrespro/rum/releases
4. $ git clone https://github.com/postgrespro/rum
5. $ cd rum
6. $ make USE_PGXS=1
7. $ make USE_PGXS=1 install
8. $ make USE_PGXS=1 installcheck
9. $ psql DB -c "CREATE EXTENSION rum;"

复制代码

创建测试表和索引

1. create or replace function to_timestamp(text) returns timestamp as $$
2.   select $1::timestamp;
3. $$ language sql strict immutable;
5. create table tbl(id int, js jsonb);
6. create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);
7. create index idx_tbl_2 on tbl using gin (js);
8. create index idx_tbl_3 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ((js->>'k3')::numeric) );

复制代码

? 操作符，匹配JSON对象中的键走索引：

1. postgres=# explain analyze select * from tbl where js ? 'a';
2.                                                     QUERY PLAN                                                   
3. ------------------------------------------------------------------------------------------------------------------
4. Bitmap Heap Scan on tbl  (cost=8.01..12.02 rows=1 width=36) (actual time=0.031..0.033 rows=0 loops=1)
5.    Recheck Cond: (js ? 'a'::text)
6.    ->  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)
7.          Index Cond: (js ? 'a'::text)
8. Planning Time: 0.581 ms
9. Execution Time: 0.133 ms
10. (6 rows)

复制代码

@> 操作符，匹配JSON对象中的键值对走索引：

1. postgres=# explain analyze select * from tbl where js @> '{"a":"b"}';
2.                                                     QUERY PLAN                                                   
3. ------------------------------------------------------------------------------------------------------------------
4. Bitmap Heap Scan on tbl  (cost=8.01..12.02 rows=1 width=36) (actual time=0.017..0.017 rows=0 loops=1)
5.    Recheck Cond: (js @> '{"a": "b"}'::jsonb)
6.    ->  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)
7.          Index Cond: (js @> '{"a": "b"}'::jsonb)
8. Planning Time: 0.132 ms
9. Execution Time: 0.046 ms
10. (6 rows)

复制代码

@> 操作符，嵌套数组使用索引

1. 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]}}';
2. ?column?
3. ----------
4. t
5. (1 row)
6. 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类型，范围查询走索引：

1. explain analyze select * from tbl where to_timestamp(js->>'k2') between '2018-01-01' and '2018-01-02';
2.                                                                                                   QUERY PLAN                                    
3.                                                                
4. ------------------------------------------------------------------------------------------------------------------------------------------------
5. ---------------------------------------------------------------
6. Bitmap Heap Scan on tbl  (cost=24.07..36.58 rows=6 width=36) (actual time=0.040..0.041 rows=0 loops=1)
7.    Recheck Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text
8. )) <= '2018-01-02 00:00:00'::timestamp without time zone))
9.    ->  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)
10.          Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::
11. text)) <= '2018-01-02 00:00:00'::timestamp without time zone))
12. Planning Time: 0.569 ms
13. Execution Time: 0.090 ms
14. (6 rows)
17. 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;
18.                                                                                                                                                 
19.      QUERY PLAN                                                                                                                                 
20.                      
21. ------------------------------------------------------------------------------------------------------------------------------------------------
22. ------------------------------------------------------------------------------------------------------------------------------------------------
23. ---------------------
24. 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)
25.    Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text))
26. <= '2018-01-02 00:00:00'::timestamp without time zone) AND (((js ->> 'k3'::text))::numeric >= '1'::numeric) AND (((js ->> 'k3'::text))::numeric
27. <= '200'::numeric))
28. Planning Time: 0.299 ms
29. Execution Time: 0.062 ms
30. (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 索引实践 - 检索(存在、包含、等值、范围等)加速
https://billtian.github.io/digoal.blog/2018/07/31/01.html

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4