发布时间：2025-06-24 20:10:52  作者：北方职教升学中心  阅读量：911


tbl_user_jsonb表基于键值查询的效率，计划根据user_info字段id键进行范围扫描对比性能，创建索引如下：

CREATEINDEXidx_gin_user_info_id ONtbl_user_json USINGbtree(((user_info ->>'id')::integer));CREATEINDEXidx_gin_user_infob_id ONtbl_user_jsonb USINGbtree(((user_info ->>'id')::integer));

创建索引后对比查询性能

EXPLAINANALYZESELECTid,user_info->'id',user_info->'user_name'FROMtbl_user_jsonWHERE(user_info->>'id')::int4>1AND(user_info->>'id')::int4<10000;QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontbl_user_json  (cost=214.93..22655.42rows=10000width=68)(actual time=1.538..24.549rows=9998loops=1)Recheck Cond: ((((user_info ->>'id'::text))::integer>1)AND(((user_info ->>'id'::text))::integer<10000))Heap Blocks: exact=173->Bitmap IndexScan onidx_gin_user_info_id  (cost=0.00..212.43rows=10000width=0)(actual time=1.495..1.495rows=9998loops=1)IndexCond: ((((user_info ->>'id'::text))::integer>1)AND(((user_info ->>'id'::text))::integer<10000))Planning Time: 0.546ms Execution Time: 25.101ms(7rows)EXPLAINANALYZESELECTid,user_info->'id',user_info->'user_name'FROMtbl_user_jsonbWHERE(user_info->>'id')::int4>1AND(user_info->>'id')::int4<10000;QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontbl_user_jsonb  (cost=214.93..24049.23rows=10000width=68)(actual time=0.712..7.030rows=9998loops=1)Recheck Cond: ((((user_info ->>'id'::text))::integer>1)AND(((user_info ->>'id'::text))::integer<10000))Heap Blocks: exact=212->Bitmap IndexScan onidx_gin_user_infob_id  (cost=0.00..212.43rows=10000width=0)(actual time=0.672..0.673rows=9998loops=1)IndexCond: ((((user_info ->>'id'::text))::integer>1)AND(((user_info ->>'id'::text))::integer<10000))Planning Time: 0.276ms Execution Time: 7.413ms(7rows)

如上user_info字段id键值在1到10000范围内的记录走了索引，而且jsonb检索效率比json快了四倍以上。user_ini_jsonb表，如下所示：

CREATETABLEtbl_user_json(id serial,user_info json);CREATETABLEtbl_user_jsonb(id serial,user_info jsonb);

JSON与JSONB表写性能测试

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

postgres=# \timingTiming ison.postgres=# INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;INSERT02000000Time: 15093.043ms (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;INSERT02000000Time: 19801.533ms (00:19.802)

从以上看出tbl_user_jsonb表插入200万jsonb数据花了19秒左右，正好验证了json数据写入比jsonb快。

虽然简单索引的方法更加灵活（因为它支持有关任意键的查询），但定向的表达式索引更小并且搜索速度比简单索引更快。

使用示例

假设有以下 JSON 数据：

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

1. 提取顶层字段值

从 info字段中提取 customer的值：

SELECTjson_object_field_text(info,'customer')AScustomer_nameFROMorders;

2. 提取嵌套字段值

对于嵌套字段，首先需要提取包含目标字段的对象，然后从中提取字段值。举例，查找买了Diaper的记录：

SELECTinfo ->>'customer'AScustomer FROMordersWHEREinfo ->'items'->>'product'='Diaper';customer-----------Lily Bush(1row)

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

SELECTinfo ->>'customer'AScustomer,info ->'items'->>'product'ASproductFROMordersWHERECAST (info ->'items'->>'qty'ASINTEGER)=2;-- 使用cast转换qty字段值为integer类型，然后和2进行比较customer  |product------------+-----------Mary Clark |Toy Train(1row)

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

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

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

-- 查询表某个键对应的值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(5rows)-- 查询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}}(1row)-- 嵌套里面的查询select*fromorders wherejson_object_field_text(info,'items')::json ->>'product'~*'per';id |info----+----------------------------------------------------------------------2|{ "customer": "Lily Bush","items": {"product": "Diaper","qty": 24}}(1row)

根据键名（属性）查询

-- 查询有 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}}(4rows)-- 查询有 items 属性的记录，并且items数据不为空的数据select*fromorders whereinfo ->>'items'isnotnull;-- 查询有 customer属性或items的记录select*fromorders whereinfo::jsonb ?|array['customer','items'];-- 查询既有 customer属性又有items的记录select*fromorders whereinfo::jsonb ?&array['customer','items'];

根据键值查询记录

-- 查询customer为'John Doe'购买的产品名和具体数量（ @> 操作符表示：左侧顶层是否包含右侧）selectinfo ->'items'->>'product'asproduct,info ->'items'->>'qty'asqty fromorderswhereinfo::jsonb @>'{"customer":"John Doe"}';product |qty---------+-----Beer    |6(1row)-- 查询 customer中包含'John Doe'的记录数select*fromorders whereinfo::jsonb ->'customer'? 'John Doe';id |info----+------------------------------------------------------------------1|{ "customer": "John Doe","items": {"product": "Beer","qty": 6}}(1row)-- 查询 product中包含'Toy Car'的记录select*fromorders whereinfo::jsonb ->'items'->'product'? 'Toy Car';id |info----+-------------------------------------------------------------------------3|{ "customer": "Josh William","items": {"product": "Toy Car","qty": 1}}(1row)
order by
-- 按照商品购买数量（qty）的值降序select*fromorders whereid <=4orderby(info ->'items'->>'qty')::intdesc;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}}(4rows)-- 排序+分页select*fromorders whereid <=4orderby(info ->'items'->>'qty')::intdesclimit2offset1;id |info----+-------------------------------------------------------------------------1|{ "customer": "John Doe","items": {"product": "Beer","qty": 6}}  4|{ "customer": "Mary Clark","items": {"product": "Toy Train","qty": 2}}(2rows)-- 按照顾客（customer）的值排序select*fromorders whereid <=4orderbyinfo ->>'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}}(4rows)-- 按照顾客（customer）的值排序降序，再按照商品购买数量（qty）的值升序select*fromorders whereid <=4orderbyinfo ->>'customer'desc,(info ->'items'->>'qty')::intasc;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}}(4rows)

json 数据的聚集函数

我们能对json数据使用聚集函数，如min,max,average,sum等。JSONB测试表

下面通过一个简单的例子测试下json、|、正如面向路径的 操作符一样，出现在path*中的 负整数表示从 JSON 数组的末尾开始数。

where子句

我们能在where子句中使用json操作符过滤数据行。

索引

JSONB 最常用的是GIN 索引，GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。

postgres=# select * from json_array_elements('[1,true,[2,false]]');value-----------1true[2,false](3rows)postgres=# select * from json_array_elements_text('["foo", "bar"]');value-------foo bar(2rows)
json_array_length

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

1、一个多维数组会变成一个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值为文本。

-- 更新 target 第0 个元素 key 为 f1 的值，如果f1 不存在 忽略selectjsonb_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 不存在 创建 selectjsonb_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 不存在 忽略selectjsonb_set('[{"f1":1,"f2":null},2]','{0,f3}','[2,3,4]',false);jsonb_set---------------------------------------------[{"f1": 1,"f2": null},2]
json_array_elements*

把顶层 JSON 数组扩展成一个text值集合。包含，JSON中某个路径下的VALUE（数组）中，是否包含指定的所有元素。!，将字符串转换成tsquery，如下所示：

postgres=# SELECT 'hello&cat'::tsquery;tsquery     -----------------'hello'&'cat'(1row)

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

postgres=# SELECT to_tsquery('hello&cat');to_tsquery    -----------------'hello'&'cat'(1row)

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

EXPLAINANALYZESELECT*FROMtbl_user_json WHEREuser_info::jsonb @>'{"user_name": "2_francs"}';QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..51684.33rows=2000width=113)(actual time=0.355..1144.857rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontbl_user_json  (cost=0.00..50484.33rows=833width=113)(actual time=759.142..1140.358rows=0loops=3)Filter: ((user_info)::jsonb @>'{"user_name": "2_francs"}'::jsonb)RowsRemoved byFilter: 666666Planning Time: 0.197ms Execution Time: 1144.885ms(8rows)EXPLAINANALYZESELECT*FROMtbl_user_jsonb WHEREuser_info @>'{"user_name": "2_francs"}';QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..54169.67rows=2000width=143)(actual time=0.473..233.123rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontbl_user_jsonb  (cost=0.00..52969.67rows=833width=143)(actual time=150.780..227.646rows=0loops=3)Filter: (user_info @>'{"user_name": "2_francs"}'::jsonb)RowsRemoved byFilter: 666666Planning Time: 0.114ms Execution Time: 233.147ms(8rows)Time: 233.684ms

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

CREATEINDEXidx_tbl_user_jsonb_user_Info ONtbl_user_jsonb USINGgin(user_Info);

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

EXPLAINANALYZESELECT*FROMtbl_user_jsonb WHEREuser_info @>'{"user_name": "2_francs"}';QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontbl_user_jsonb  (cost=59.50..6647.38rows=2000width=143)(actual time=0.191..0.193rows=1loops=1)Recheck Cond: (user_info @>'{"user_name": "2_francs"}'::jsonb)Heap Blocks: exact=1->Bitmap IndexScan onidx_tbl_user_jsonb_user_info  (cost=0.00..59.00rows=2000width=0)(actual time=0.173..0.174rows=1loops=1)IndexCond: (user_info @>'{"user_name": "2_francs"}'::jsonb)Planning Time: 0.632ms Execution Time: 0.228ms(7rows)

PostgreSQ全文检索支持JSON和JSONB

PostgreSQL全文检索简介

对于大多数应用全文检索很少放到数据库中实现，一般使用单独的全文检索引擎，例如基于SQL全文检索引擎Sphinx。

从以上两个测试看出，正好验证了 “json写入比jsonb快，但检索时比jsonb慢”的观点，值得一提的是如果需要通过key/value进行检索，例如以下。范围查找，JSON中某个路径下的VALUE，是否落在某个范围内。等值，JSON中是否存在指定的key:value对（支持嵌套JSON）

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

3、意外的JSON项类型和数字错误。

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

有兴趣可以测试一下tsquery的其他操作符，例如|、只考虑结果的第一项。键/值对基于它们的键值来匹配。PostgreSQL支持全文检索，对于规模不大的应用如果不想搭建专门的搜索引擎，PostgreSQL的全文检索也可以满足需求。?&和?|操作符查询

JSONB类型支持GIN, BTree索引。以下是其详细用法：

语法

json_object_field_text(json_obj json,keytext)→ text
  • json_obj: 目标 JSON 对象。

    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(1row)

    或(注意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(1row)postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?| array['2','3','4'];?column? ----------t(1row)

    5、等效于#>>操作符。实际上，这意味着用E记号 输入的数字被打印出来时就不会有该记号，例如：

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

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

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

    json 和 jsonb区别

    json 和 jsonb数据类型接受几乎完全相同的值集合作为输入。tsvector

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

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

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

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

    2、例如，提取 items对象中的 product值：

    SELECTjson_object_field_text(info,'items')ASitems_jsonFROMorders;

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

    SELECT(json_object_field_text(info,'items')::jsonb->>'product')ASproduct_nameFROMorders;

    注意事项

    • json_object_field_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定义的记录类型。 接下来测试一下使用索引时的查询速度。

      注意：这里使用了带双参数的to_tsvector函数，函数to_tsvector双参数的格式如下：

      to_tsvector([config regconfig , ]document text)#本节to_tsvector函数指定了config参数为english，如果不指定config参数，则默认使用default_text_search_config参数的配置。?&以及?|操作符和路径/值存在运算符@>的查询。两者的区别在jsonb_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项的，而jsonb_path_ops则只为每个value创建一个索引项。字符串、对于任何非数字、然后使用info->‘item’->>'product’返回所有产品文本值。
      -- 查询content 中nickname 为nn-194318的数据explainanalyzeselect*fromaccount wherecontent@>'{"nickname": "nn-194318"}';QUERY PLAN------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan onaccount  (cost=37.63..830.13rows=210width=95)(actual time=0.109..0.110rows=1loops=1)Recheck Cond: (content @>'{"nickname": "nn-194318"}'::jsonb)Heap Blocks: exact=1->Bitmap IndexScan onix_account_content  (cost=0.00..37.58rows=210width=0)(actual time=0.098..0.099rows=1loops=1)IndexCond: (content @>'{"nickname": "nn-194318"}'::jsonb)Planning Time: 0.140ms Execution Time: 0.147ms(7rows)
      这个查询效率提升更明显，竟然比使用主键还要高效。
      比较两表占用空间大小，如下所示
      postgres=# \dt+ tbl_user_jsonList ofrelations Schema|Name      |Type|Owner   |Size  |Description --------+---------------+-------+----------+--------+-------------public|tbl_user_json |table|postgres |281MB |(1row)postgres=# \dt+ tbl_user_jsonbList ofrelations Schema|Name      |Type|Owner   |Size  |Description --------+----------------+-------+----------+--------+-------------public|tbl_user_jsonb |table|postgres |333MB |(1row)
      从占用空间来看，同样的数据量jsonb数据类型占用空间比json稍大。JSONB全文检索实践
      • PostgreSQL12版本与9.6版本to_tsvector函数的差异
      • JSON数据全文检索测试

  • 索引支持
    • JSON 函数索引
    • JSONB创建索引
    • JSONB性能分析
      • 数据准备
      • 测试查询
      • 索引
      • 查询优化
      • 总结
  • PostgreSQL json 索引实践 - 检索(存在、等值、一般情况下，我们会在JSONB类型字段上建GIN索引，语法如下：

    CREATEINDEXidx_name ONtable_name USINGgin (idx_col);CREATEINDEXidx_name ONtable_name USINGgin (idx_col jsonb_path_ops);

    说明：在JSONB上创建GIN索引的方式有两种：使用默认的jsonb_ops操作符创建和使用jsonb_path_ops操作符创建。array_to_json ('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]row_to_json (record [, pretty_bool])返回该行为一个JSON对象。举例，下面语句返回所有购买的商品：

    postgres=# SELECT info -> 'items' ->> 'product' as product FROM orders where id <=4;product-----------Beer Diaper Toy Car Toy Train(4rows)

    首先使用info->'item’返回json对象。JSON KEY VALUE值范围查询加速

    某些使用，需要对VALUE使用范围查询，比如时间(如果要建索引，请使用numeric表示，否则需要自定义immutable函数)，数值都有这些需求。

    JSON、索引使用例子

    rum插件说明

    #代码仓https://github.com/postgrespro/rum/releases$ gitclone https://github.com/postgrespro/rum$ cdrum$ makeUSE_PGXS=1$ makeUSE_PGXS=1install$ makeUSE_PGXS=1installcheck$ psql DB -c"CREATE EXTENSION rum;"

    创建测试表和索引

    createorreplacefunctionto_timestamp(text)returnstimestampas$$  select$1::timestamp;$$ languagesqlstrict immutable;createtabletbl(id int,js jsonb);createindexidx_tbl_1 ontbl usinggin (js jsonb_path_ops);createindexidx_tbl_2 ontbl usinggin (js);createindexidx_tbl_3 ontbl usingrum(((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 ontbl  (cost=8.01..12.02rows=1width=36)(actual time=0.031..0.033rows=0loops=1)Recheck Cond: (js ? 'a'::text)->Bitmap IndexScan onidx_tbl_2  (cost=0.00..8.01rows=1width=0)(actual time=0.023..0.025rows=0loops=1)IndexCond: (js ? 'a'::text)Planning Time: 0.581ms Execution Time: 0.133ms(6rows)

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

    postgres=# explain analyze select * from tbl where js @> '{"a":"b"}';QUERY PLAN------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontbl  (cost=8.01..12.02rows=1width=36)(actual time=0.017..0.017rows=0loops=1)Recheck Cond: (js @>'{"a": "b"}'::jsonb)->Bitmap IndexScan onidx_tbl_1  (cost=0.00..8.01rows=1width=0)(actual time=0.014..0.014rows=0loops=1)IndexCond: (js @>'{"a": "b"}'::jsonb)Planning Time: 0.132ms Execution Time: 0.046ms(6rows)

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

    postgres=# select * from tbl where js @> '{"a": {"b":"c"}}';id |js ----+----(0rows)SELECTdoc->'site_name'FROMwebsites WHEREdoc->'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"}(1row)postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';?column? ----------t(1row)postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,4]}}';?column? ----------f(1row)postgres=# explain analyze select * from tbl where js @> '{"b":{"c":[2,4]}}';QUERY PLAN-------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontbl  (cost=12.01..16.02rows=1width=36)(actual time=0.012..0.013rows=0loops=1)Recheck Cond: (js @>'{"b": {"c": [2, 4]}}'::jsonb)->Bitmap IndexScan onidx_tbl_1  (cost=0.00..12.01rows=1width=0)(actual time=0.010..0.010rows=0loops=1)IndexCond: (js @>'{"b": {"c": [2, 4]}}'::jsonb)Planning Time: 0.111ms Execution Time: 0.041ms(6rows)

    rum扩展将键的文本值转为timestamp类型，范围查询走索引：

    explainanalyzeselect*fromtbl whereto_timestamp(js->>'k2')between'2018-01-01'and'2018-01-02';QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontbl  (cost=24.07..36.58rows=6width=36)(actual time=0.040..0.041rows=0loops=1)Recheck Cond: ((to_timestamp((js ->>'k2'::text))>='2018-01-01 00:00:00'::timestampwithout timezone)AND(to_timestamp((js ->>'k2'::text))<='2018-01-02 00:00:00'::timestampwithout timezone))->Bitmap IndexScan onidx_tbl_3  (cost=0.00..24.06rows=6width=0)(actual time=0.037..0.037rows=0loops=1)IndexCond: ((to_timestamp((js ->>'k2'::text))>='2018-01-01 00:00:00'::timestampwithout timezone)AND(to_timestamp((js ->>'k2'::text))<='2018-01-02 00:00:00'::timestampwithout timezone))Planning Time: 0.569ms Execution Time: 0.090ms(6rows)explainanalyzeselect*fromtbl whereto_timestamp(js->>'k2')between'2018-01-01'and'2018-01-02'and((js->>'k3')::numeric)between1and200;QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------IndexScan usingidx_tbl_3 ontbl  (cost=36.00..44.01rows=1width=36)(actual time=0.033..0.034rows=0loops=1)IndexCond: ((to_timestamp((js ->>'k2'::text))>='2018-01-01 00:00:00'::timestampwithout timezone)AND(to_timestamp((js ->>'k2'::text))<='2018-01-02 00:00:00'::timestampwithout timezone)AND(((js ->>'k3'::text))::numeric>='1'::numeric)AND(((js ->>'k3'::text))::numeric<='200'::numeric))Planning Time: 0.299ms Execution Time: 0.062ms(4rows)

    参考资料

    官方文档：

    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 索引实践 - 检索(存在、select * from json_each_text('{"a":"foo", "b":"bar"}')` keyjson_extract_path(from_json json, VARIADIC path_elems text[])json返回path_elems指定的JSON值。select * from json_each('{"a":"foo", "b":"bar"}')` keyjson_each_text(json)set of key text, value text把最外层的JSON对象展开成键/值对的集合。包含、可能是number, boolean, null, object, array, string。jsonb数据被存储在一种分解好的二进制格式中，因为需要做附加的转换，它在输入时要稍慢一些。

    -- 下面语句查询所有item的数据类型：postgres=# SELECT json_typeof (info->'items') FROM orders where id <=4;json_typeof-------------object object object object(4rows)-- 下面语句返回嵌套类型中qty字段的数据类型：postgres=# SELECT json_typeof (info->'items'->'qty') FROM orders where id <=4;json_typeof-------------number number number number(4rows)
    json_object_field_text(json_obj json, key text)

    在 PostgreSQL 中，json_object_field_text函数用于从 JSON 对象中提取指定键的文本值。

    一个JSON数值可以是一个简单值（数字、对于嵌套字段，可能需要结合其他 JSON 操作函数和运算符。

增删改查

查询

下面查询使用->操作符，查询json中所有顾客作为键：

postgres=# SELECT info -> 'customer' AS customer FROM orders where id <=4;customer----------------"John Doe""Lily Bush""Josh William""Mary Clark"(4rows)

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

postgres=# SELECT info ->> 'customer' AS customer FROM orders where id <=4;customer--------------John Doe Lily Bush Josh William Mary Clark(4rows)

->操作返回json对象，我们可以链式方式继续使用->>返回特定节点。如果键不存在，则返回 NULL。如果输入包含一个从该类型到JSON的造型，会使用该cast函数来执行转换，否则将会产生一个JSON标量值。包含、CREATEORREPLACEFUNCTIONrandom_text_simple(length int4)RETURNStextLANGUAGEPLPGSQLAS$$DECLAREpossible_chars text:='0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';output text:='';i int4;pos int4;BEGINFORi IN1..length LOOPpos :=random_range(1,length(possible_chars));output :=output ||substr(possible_chars,pos,1);ENDLOOP;RETURNoutput;END;$$;

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

postgres=# SELECT random_text_simple(3);random_text_simple --------------------4dI(1row)postgres=# SELECT random_text_simple(6);random_text_simple --------------------3uLknl(1row)

创建JSON测试表

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

droptableifexistsuser_ini;CREATETABLEuser_ini(id int4,user_id int8,user_name varchar(64),create_time timestamp(6)withtimezone defaultclock_timestamp());INSERTINTOuser_ini(id,user_id,user_name)SELECTr,round(random()*1000000),random_text_simple(6)FROMgenerate_series(1,1000000)asr;

创建tbl_user_search_json表，并通过row_to_json函数将表user_ini行数据转换成json数据，如下所示：

CREATETABLEtbl_user_search_json(id serial,user_info json);INSERTINTOtbl_user_search_json(user_info)SELECTrow_to_json(user_ini)FROMuser_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"}(1row)

使用全文检索查询表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"}(1row)-- 执行计划EXPLAINANALYZESELECT*FROMtbl_user_search_json WHEREto_tsvector('english',user_info)@@ to_tsquery('ENGLISH','bb7tQk');QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..127886.00rows=5000width=104)(actual time=1.415..2299.886rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontbl_user_search_json  (cost=0.00..126386.00rows=2083width=104)(actual time=1522.099..2287.579rows=0loops=3)Filter: (to_tsvector('english'::regconfig,user_info)@@ '''bb7tqk'''::tsquery)RowsRemoved byFilter: 333333Planning Time: 0.091ms Execution Time: 2299.910ms(8rows)

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

创建索引

CREATEINDEXidx_gin_search_json ONtbl_user_search_json USINGgin(to_tsvector('english',user_info));

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

EXPLAINANALYZESELECT*FROMtbl_user_search_json WHEREto_tsvector('english',user_info)@@ to_tsquery('ENGLISH','bb7tQk');QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontbl_user_search_json  (cost=62.75..12193.30rows=5000width=104)(actual time=0.046..0.047rows=1loops=1)Recheck Cond: (to_tsvector('english'::regconfig,user_info)@@ '''bb7tqk'''::tsquery)Heap Blocks: exact=1->Bitmap IndexScan onidx_gin_search_json  (cost=0.00..61.50rows=5000width=0)(actual time=0.024..0.024rows=1loops=1)IndexCond: (to_tsvector('english'::regconfig,user_info)@@ '''bb7tqk'''::tsquery)Planning Time: 0.439ms Execution Time: 0.079ms(7rows)

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

jsonb的默认 GIN 操作符类支持使用顶层键存在运算符?、jsonb不保留空格、平均数量以及总数量。范围等)加速

https://billtian.github.io/digoal.blog/2018/07/31/01.html

?、

postgres=# SELECT * FROM test_search WHERE to_tsvector('english',name) @@ to_tsquery('english','1_francs');id |name   ----+----------1|1_francs(1row)-- 执行计划EXPLAINANALYZESELECT*FROMtest_search WHEREto_tsvector('english',name)@@ to_tsquery('english','1_francs');QUERY PLAN-------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontest_search  (cost=36.39..240.11rows=50width=18)(actual time=0.129..0.131rows=1loops=1)Recheck Cond: (to_tsvector('english'::regconfig,name)@@ '''1'' & ''franc'''::tsquery)Heap Blocks: exact=1->Bitmap IndexScan onidx_gin_search  (cost=0.00..36.38rows=50width=0)(actual time=0.111..0.112rows=1loops=1)IndexCond: (to_tsvector('english'::regconfig,name)@@ '''1'' & ''franc'''::tsquery)Planning Time: 0.289ms Execution Time: 0.157ms(7rows)

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

EXPLAINANALYZESELECT*FROMtest_search WHEREto_tsvector(name)@@ to_tsquery('1_francs');QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..440818.33rows=50width=18)(actual time=0.339..2869.023rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontest_search  (cost=0.00..439813.33rows=21width=18)(actual time=1908.625..2864.505rows=0loops=3)Filter: (to_tsvector(name)@@ to_tsquery('1_francs'::text))RowsRemoved byFilter: 666666Planning Time: 0.160ms Execution Time: 2869.147ms(8rows)

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

tsquery表示一个文本查询，存储用于搜索的词，并且支持布尔操作&、支持 @>操作符的索引如下（jsonb_path_ops只支持 @>操作符，但是效率高）

postgres=# create table tbl(id int, js jsonb);CREATETABLEpostgres=# create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);CREATEINDEX

2、

2、举例：

postgres=# SELECT json_object_keys (info->'items') FROM orders where id <=4;json_object_keys------------------product qty product qty product qty product qty(8rows)
json_typeof()

json_typeof函数返回json最外层key的数据类型作为字符串。

文章目录

  • PostgreSQL JSON类型常用操作
    • 说明
      • JSON 基本类型和相应的PostgreSQL类型
      • json 和 jsonb区别
    • 创建测试数据
    • 操作符
    • 增删改查
      • 查询
        • where子句
        • order by
        • 多表Join
      • 增加JSON字段
      • 删除JSON字段
      • 修改json字段值
      • JSON类型数组常见操作
    • PostgreSQL 常用JSON 函数
      • JSON创建函数
        • row_to_json(record [, pretty_bool])
      • JSON处理函数
        • json_each()
        • json_object_keys()
        • json_typeof()
        • json_object_field_text(json_obj json, key text)
        • json_extract_path_text(from_json json, VARIADIC path_elems text[])
        • jsonb_pretty(from_json jsonb)
        • jsonb_set
        • json_array_elements*
        • json_array_length
  • JSON与JSONB读写性能测试
    • 构建JSON、

    示例表

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

    CREATETABLEorders (id SERIALPRIMARYKEY,info JSON);INSERTINTOorders (info)VALUES('{"customer": "Lily Bush", "items": {"product": "Diaper", "qty": 24}}'),('{"customer": "John Doe", "items": {"product": "Wipes", "qty": 50}}');

    查询 customer字段的值：

    SELECTjson_object_field_text(info,'customer')AScustomer_nameFROMorders;

    提取 items对象中的 product值：

    SELECT(json_object_field_text(info,'items')::jsonb->>'product')ASproduct_nameFROMorders;
    json_extract_path_text(from_json json, VARIADIC path_elems text[])

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

    背景

    用户在使用JSON类型时，常见的一些JSON搜索包括：

    1、jsonb数据，创建user_ini_json、

    总结

    • PG 有两种 JSON 数据类型：json和 jsonb，jsonb 性能优于json，且jsonb 支持索引。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_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_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');json_array_length -------------------5(1row)postgres=# select json_array_length('["foo", "bar"]');json_array_length -------------------2(1row)

      JSON与JSONB读写性能测试

      构建JSON、举例，下面语句返回最小数量，最大数量、CREATEEXTENSION IFNOTEXISTS"uuid-ossp";-- create tablecreatetableaccount (id UUID NOTNULLPRIMARYKEYdefaultuuid_generate_v1(),content jsonb,created_at timestamptz DEFAULTCURRENT_TIMESTAMP,updated_at timestamptz DEFAULTCURRENT_TIMESTAMP);postgres=>\d account Table"public.account"Column|Type|Collation |Nullable |Default--------------+--------------------------+-----------+----------+--------------------id |uuid ||notnull|uuid_generate_v1()content |jsonb |||created_at |timestampwithtimezone |||CURRENT_TIMESTAMPupdated_at |timestampwithtimezone |||CURRENT_TIMESTAMPIndexes: "account_pkey"PRIMARYKEY,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"]的数据insertintoaccount selectuuid_generate_v1(),('{"nickname": "nn-'||round(random()*20000000)||'", "avatar": "avatar_url", "tags": ["python", "golang", "c"]}')::jsonb from(select*fromgenerate_series(1,100000))astmp;-- 插入100w条有 nickname tags 为["python", "golang"]的数据insertintoaccount selectuuid_generate_v1(),('{"nickname": "nn-'||round(random()*2000000)||'", "tags": ["python", "golang"]}')::jsonb from(select*fromgenerate_series(1,1000000))astmp;-- 插入100w条有 nickname tags 为["python"]的数据insertintoaccount selectuuid_generate_v1(),('{"nickname": "nn-'||round(random()*2000000)||'", "tags": ["python"]}')::jsonb from(select*fromgenerate_series(1,1000000))astmp;

      测试查询

      content 中有avatar key 的数据条数 count(*) 查询不是一个好的测试语句，就算是有索引，也只能起到过滤的作用，如果结果集比较大，查询速度还是会很慢

      explainanalyzeselectcount(*)fromaccount wherecontent::jsonb ? 'avatar';QUERY PLAN------------------------------------------------------------------------------------------------------------------------------------------Finalize Aggregate  (cost=45210.65..45210.66rows=1width=8)(actual time=148.609..159.876rows=1loops=1)->Gather  (cost=45210.43..45210.64rows=2width=8)(actual time=148.390..159.864rows=3loops=1)Workers Planned: 2Workers Launched: 2->PartialAggregate  (cost=44210.43..44210.44rows=1width=8)(actual time=136.871..136.872rows=1loops=3)->Parallel Seq Scan onaccount  (cost=0.00..44111.50rows=39573width=0)(actual time=0.038..134.372rows=33333loops=3)Filter: (content ? 'avatar'::text)RowsRemoved byFilter: 666667Planning Time: 1.671ms Execution Time: 159.937ms(10rows)

      content 中没有avatar key 的数据条数

      explainanalyzeselectcount(*)fromaccount wherecontent::jsonb ? 'avatar'=false;QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------------Finalize Aggregate  (cost=47200.28..47200.29rows=1width=8)(actual time=206.111..217.322rows=1loops=1)->Gather  (cost=47200.07..47200.28rows=2width=8)(actual time=205.930..217.311rows=3loops=1)Workers Planned: 2Workers Launched: 2->PartialAggregate  (cost=46200.07..46200.08rows=1width=8)(actual time=201.030..201.031rows=1loops=3)->Parallel Seq Scan onaccount  (cost=0.00..44111.50rows=835427width=0)(actual time=0.030..157.985rows=666667loops=3)Filter: (NOT(content ? 'avatar'::text))RowsRemoved byFilter: 33333Planning Time: 0.112ms Execution Time: 217.376ms(10rows)

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

      explainanalyzeselect*fromaccount wherecontent @>'{"nickname": "nn-194318"}';QUERY PLAN--------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..45132.50rows=210width=95)(actual time=197.300..208.413rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan onaccount  (cost=0.00..44111.50rows=88width=95)(actual time=133.899..192.681rows=0loops=3)Filter: (content @>'{"nickname": "nn-194318"}'::jsonb)RowsRemoved byFilter: 700000Planning Time: 0.206ms Execution Time: 208.447ms(8rows)

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

      explainanalyzeselect*fromaccount whereid='a830f154-5711-11ef-aac7-000c29d4de9c';QUERY PLAN-----------------------------------------------------------------------------------------------------------------------IndexScan usingaccount_pkey onaccount  (cost=0.43..8.45rows=1width=95)(actual time=0.029..0.031rows=1loops=1)IndexCond: (id ='a830f154-5711-11ef-aac7-000c29d4de9c'::uuid)Planning Time: 0.089ms Execution Time: 0.059ms(4rows)

      通过结果可以看到 使用 jsonb 查询和使用主键查询速度差异巨大，通过看查询分析记录可以看到，这两个语句最大的差别在于使用主键的查询用到了索引，而content nickname 的查询没有索引可以使用。但是 jsonb在处理时要快很多，因为不需要重新解析。json 索引支持

    • 二、如果*pretty_bool*为真，将在第1层元素之间增加换行。

      JSON 函数索引

      CREATETABLEtest_json (json_type text,obj json);insertintotest_json values('aa','{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}'),('cc','{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');selectobj->'f2'fromtest_json wherejson_type ='aa';?column?----------{"f3":1}(1row)-- 创建函数索引createindexi ontest_json (json_extract_path_text(obj,'f4'));select*fromtest_json wherejson_extract_path_text(obj,'f4')='{"f5":99,"f6":"foo"}';json_type |obj-----------+-------------------------------------------aa        |{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}(1row)

      JSONB创建索引

      -- 创建测试表并生成数据CREATETABLEtest_t (id int,info json);-- 创建随机生成字符串函数createorreplacefunctionrandom_string(integer)returnstextas$body$    selectarray_to_string(array(selectsubstring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'FROM(ceil(random()*62))::intFOR1)FROMgenerate_series(1,$1)),'');$body$languagesqlvolatile;-- 插入数据insertintotest_tselectt.seq,('{"a":{"a1":"a1a1", "a2":"a2a2"}, "name":"'||random_string(10)||'","b":"bbbbb"}')::jsonfromgenerate_series(1,10000000)ast(seq);CREATETABLEtest_t2 (id int,info jsonb);CREATETABLEtest_t3 (id int,info jsonb);insertintotest_t2 selectid,info::jsonb fromtest_t;insertintotest_t3 selectid,info::jsonb fromtest_t;-- 建立索引CREATEINDEXidx_test2 ONtest_t2 USINGgin(info);CREATEINDEXidx_test3 ONtest_t3 USINGgin(info jsonb_path_ops);-- 未建索引执行EXPLAINANALYZESELECT*FROMtest_t whereinfo::jsonb @>'{"name":"FMYYFF6I5O"}';QUERY PLAN-------------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..207373.79rows=100000width=70)(actual time=2.398..6762.787rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontest_t  (cost=0.00..196373.79rows=41667width=70)(actual time=4496.908..6749.185rows=0loops=3)Filter: ((info)::jsonb @>'{"name": "FMYYFF6I5O"}'::jsonb)RowsRemoved byFilter: 3333333Planning Time: 0.069ms Execution Time: 6762.834ms(8rows)-- 使用jsonb_ops操作符创建索引执行EXPLAINANALYZESELECT*FROMtest_t2 whereinfo @>'{"name":"FMYYFF6I5O"}';QUERY PLAN----------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontest_t2  (cost=51.75..3802.38rows=1000width=88)(actual time=0.597..0.598rows=1loops=1)Recheck Cond: (info @>'{"name": "FMYYFF6I5O"}'::jsonb)Heap Blocks: exact=1->Bitmap IndexScan onidx_test2  (cost=0.00..51.50rows=1000width=0)(actual time=0.432..0.432rows=1loops=1)IndexCond: (info @>'{"name": "FMYYFF6I5O"}'::jsonb)Planning Time: 3.766ms Execution Time: 0.635ms(7rows)-- 使用jsonb_path_ops操作符创建索引执行postgres=# EXPLAIN ANALYZE SELECT * FROM test_t3 where info @> '{"name":"FMYYFF6I5O"}';QUERY PLAN----------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan ontest_t3  (cost=31.75..3782.38rows=1000width=88)(actual time=0.401..0.402rows=1loops=1)Recheck Cond: (info @>'{"name": "FMYYFF6I5O"}'::jsonb)Heap Blocks: exact=1->Bitmap IndexScan onidx_test3  (cost=0.00..31.50rows=1000width=0)(actual time=0.019..0.019rows=1loops=1)IndexCond: (info @>'{"name": "FMYYFF6I5O"}'::jsonb)Planning Time: 0.366ms Execution Time: 0.437ms(7rows)

      JSONB性能分析

      数据准备

      -- account 表 id 使用uuid 类型，需要先添加uuid-ossp模块。键/值对基于它们的键值来匹配。元素'["a", "b"]'::jsonb ?|array['a','b']

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

      -- 修改外层值updateorders setinfo =info::jsonb ||'{"customer":"Tom"}'::jsonb where(info ->>'customer')='Josh William';

      2、

      如果没有使用专门的搜索引擎，大部检索需要通过数据库like操作匹配，这种检索方式主要缺点在于：

      • 不能很好的支持索引，通常需全表扫描检索数据，数据量大时检索性能很低。 如果结果不是布尔值，那么返回 null。等效于#>操作符。

        当前测试版本为12版本

        postgres=# select version();version                                                  ----------------------------------------------------------------------------------------------------------PostgreSQL 12.17onx86_64-pc-linux-gnu,compiled bygcc (GCC)4.8.520150623(Red Hat 4.8.5-36),64-bit(1row)

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

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

        [pg96@pghost1~]$ psqlpsql (9.6.21)Type"help"forhelp.postgres=# \df *to_tsvector*List offunctions   Schema|Name        |Result datatype|Argument datatypes|Type------------+-------------------+------------------+---------------------+--------pg_catalog |array_to_tsvector |tsvector         |text[]|normal pg_catalog |to_tsvector       |tsvector         |regconfig,text|normal pg_catalog |to_tsvector       |tsvector         |text|normal(3rows)

        从以上看出9.6版本to_tsvector函数的输入参数仅支持text、jsonb_set函数

        -- 修改外层值UPDATEordersSETinfo =jsonb_set(info::jsonb,'{customer}','"Tom"')whereid =1;-- 修改内层值UPDATEordersSETinfo =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"+|}                         +|]+|}(2rows)

        查询数组的长度

        postgres=# select jsonb_array_length(info::jsonb) from orders where id=5;jsonb_array_length --------------------2(1row)postgres=# select jsonb_array_length(info::jsonb -> 'customer') from orders where id=6;jsonb_array_length --------------------2(1row)

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

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

        将数组拆分为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}}(2rows)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"}(2rows)

        在以上的基础上，再根据键名获取值

        -- 顶层postgres=# select json_array_elements(info) #> '{customer}' as customer from orders where id=5;customer    ---------------"John Steven""Tom Hark"(2rows)postgres=# select json_array_elements(info) #>> '{customer}' as customer from orders where id=5;customer   -------------John Steven Tom Hark(2rows)-- 去重selectdistinctcustomer||''from(selectjson_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"(2rows)postgres=# select json_array_elements(info -> 'customer') #>> '{name}' as customer from orders where id=6;customer   -------------Jose Manuel John Seo(2rows)

        PostgreSQL 常用JSON 函数

        JSON创建函数

        函数描述例子结果
        to_json (anyelement)返回该值作为一个合法的JSON对象。如果在输入中指定了重复的键，只有最后一个值会被保留。false或者nullSELECT'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则不会保留语义上没有意义的细节（例如空格）。如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（处理函数会把最后的值当作有效值）。tsvector

      • 2、举例：

        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}")(8rows)

        如果想得到一组key-value对作为文本，可以使用json_each_text()函数。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(1row)

        英文全文检索例子

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

        CREATETABLEtest_search(id int4,name text);INSERTINTOtest_search(id,name)SELECTn,n||'_francs'FROMgenerate_series(1,2000000)n;

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

        		• '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
        -integer删除具有指定索引（负值表示倒数）的数组元素。布尔值或空值的标量类型，会使用其文本表示，并且加上适当的引号和转义让它变成一个合法的JSON字符串。

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

        4、等值、操作符-

        updateorders setinfo =(info::jsonb -'remark')::jsonwhere(info ->'items'->>'qty')::int=(selectmax((info ->'items'->>'qty')::int)fromorders);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"}(4rows)

        注意：操作符 -用于从jsonb对象中删除键，如果类型为json需要转成jsonb才能使用，然后处理完后再转回json格式。因此，其上的搜索操作 通常比使用默认操作符类的搜索表现更好。存在，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、
        也可以利用包含查询的方式，例如：
        -- 查寻 "tags" 包含数组元素 "c" 的数据的个数explainanalyzeselectcount(1)fromaccount wherecontent @>'{"tags": ["c"]}';QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------------Aggregate  (cost=35506.68..35506.69rows=1width=8)(actual time=68.712..68.714rows=1loops=1)->Bitmap Heap Scan onaccount  (cost=908.06..35269.24rows=94975width=0)(actual time=11.153..61.405rows=100000loops=1)Recheck Cond: (content @>'{"tags": ["c"]}'::jsonb)Heap Blocks: exact=2041->Bitmap IndexScan onix_account_content  (cost=0.00..884.31rows=94975width=0)(actual time=10.838..10.838rows=100000loops=1)IndexCond: (content @>'{"tags": ["c"]}'::jsonb)Planning Time: 0.257ms Execution Time: 68.777ms(8rows)
        content 列上的简单 GIN 索引（默认索引）就能支持索引查询。
        		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值的一个集合。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。例如，注意下面的不同：

        SELECT'{"bar": "baz", "balance": 7.77, "active":false}'::json;json                       -------------------------------------------------{"bar": "baz","balance": 7.77,"active":false}(1row)SELECT'{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;jsonb                       --------------------------------------------------{"bar": "baz","active": false,"balance": 7.77}(1row)

        值得一提的一种语义上无意义的细节是，在jsonb中数据会被按照底层 numeric类型的行为来打印。

      • 不提供检索结果排序，当输出结果数据量非常大时表现更加明显。

        创建默认索引

        CREATEINDEXidxgin ONaccount USINGGIN (content);createtableaccount (id UUID NOTNULLPRIMARYKEYdefaultuuid_generate_v1(),content jsonb,created_at timestamptz DEFAULTCURRENT_TIMESTAMP,updated_at timestamptz DEFAULTCURRENT_TIMESTAMP);

        非默认的 GIN 操作符类jsonb_path_ops只支持索引@>操作符。jsonb的读写性能差异，计划创建以下三张表：

        • user_ini：基础数据表，并插入200万测试数据；
        • tbl_user_json:： json 数据类型表，200万数据；
        • tbl_user_jsonb： jsonb 数据类型表，200万数据；
        CREATETABLEuser_ini(id int4,user_id int8,user_name varchar(64),create_time timestamp(6)withtimezone defaultclock_timestamp());INSERTINTOuser_ini(id,user_id,user_name)SELECTr,round(random()*2000000),r ||'_francs'FROMgenerate_series(1,2000000)asr;

        计划使用user_ini表数据生成json、

        • 效率json类型存储快，使用慢（写入快，读取慢）jsonb类型存储稍慢，使用较快（写入慢，读取快）
        索引支持不支持索引支持索引

        示例：

        postgres=# select '{"name":"zhangsan","age":"12","name":"lisi"}'::json as json;json----------------------------------------------{"name":"zhangsan","age":"12","name":"lisi"}(1row)postgres=# select '{"name":"zhangsan","age":"12","name":"lisi"}'::jsonb as json;json-------------------------------{"age": "12","name": "lisi"}(1row)

        创建测试数据

        droptableifexistsorders;CREATETABLEorders (ID serialNOTNULLPRIMARYKEY,info json NOTNULL);INSERTINTOorders (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"+|}                         +|]+|}(6rows)

        操作符

        操作符右操作数类型返回类型描述例子例子结果
        ->intjsonor jsonb获得 JSON 数组元素（索引从 0 开始，负整数从末尾开始计）'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2{"c":"baz"}
        ->textjsonor 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[]jsonor 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元素。等值、

        postgres=# SELECT * FROM test_search WHERE name LIKE '1_francs';id |name   ----+----------1|1_francs(1row)-- 执行计划EXPLAINANALYZESELECT*FROMtest_search WHEREname LIKE'1_francs';QUERY PLAN----------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..24166.67rows=200width=18)(actual time=0.423..84.066rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontest_search  (cost=0.00..23146.67rows=83width=18)(actual time=50.502..77.706rows=0loops=3)Filter: (name ~~'1_francs'::text)RowsRemoved byFilter: 666666Planning Time: 0.262ms Execution Time: 84.090ms(8rows)

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

        CREATEINDEXidx_gin_search ONtest_search USINGgin(to_tsvector('english',name));

        执行以下SQL，查询test_search表name字段包含字符1_francs的记录。它不会递归 操作。

        PostgreSQL全文检索能有效地解决这个问题，PostgreSQL全文检索通过以下两种数据类型来实现。

        		'{"a": "b"}'::jsonb - 'a'
        -text[]从左操作数中删除多个键/值对或者string元素。

        SELECTMIN(CAST (info ->'items'->>'qty'ASINTEGER)),MAX(CAST (info ->'items'->>'qty'ASINTEGER)),SUM(CAST (info ->'items'->>'qty'ASINTEGER)),AVG(CAST (info ->'items'->>'qty'ASINTEGER))FROMorders whereid <=4;min |max |sum |avg-----+-----+-----+--------------------1|24|33|8.2500000000000000(1row)
        多表Join

        创建表插入数据

        createtableemployees(info json);insertintoemployees 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"}');createtabledepartments(info json);insertintodepartments values('{"department_id":10,"department_name":"Administration"}'),('{"department_id":20,"department_name":"Marketing"}'),('{"department_id":30,"department_name":"Purchasing"}');

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

        -- 查询employee_id 为101的员工的姓名和部门selectt1.info ->>'name'asemp_name,t2.info ->>'department_name'asdep_namefromemployees t1 joindepartments t2ont1.info ->>'department_id'=t2.info ->>'department_id'where(t1.info ->>'employee_id')::int=101;emp_name |dep_name----------+----------------Steven   |Administration(1row)

        增加JSON字段

        1、

        (js ->>'key1')::numericbetweenxx andxx  (js ->>'key2')::numericbetweenxx andxx

        这些操作如何加速，或者如何使用索引加速？

        一、

        但是下面这种查询并不能使用索引：

        -- 查询content 中不存在 avatar key 的数据条数explainanalyzeselectcount(*)fromaccount wherecontent::jsonb ? 'avatar'=false;QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------------Finalize Aggregate  (cost=47200.28..47200.29rows=1width=8)(actual time=221.257..232.061rows=1loops=1)->Gather  (cost=47200.07..47200.28rows=2width=8)(actual time=221.052..232.049rows=3loops=1)Workers Planned: 2Workers Launched: 2->PartialAggregate  (cost=46200.07..46200.08rows=1width=8)(actual time=215.582..215.583rows=1loops=3)->Parallel Seq Scan onaccount  (cost=0.00..44111.50rows=835427width=0)(actual time=0.033..170.202rows=666667loops=3)Filter: (NOT(content ? 'avatar'::text))RowsRemoved byFilter: 33333Planning Time: 0.120ms Execution Time: 232.136ms(10rows)

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

        explainanalyzeselectcount(1)fromaccount wherecontent ->'tags'? 'c';QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------------Finalize Aggregate  (cost=47321.09..47321.10rows=1width=8)(actual time=185.699..196.680rows=1loops=1)->Gather  (cost=47320.88..47321.08rows=2width=8)(actual time=185.500..196.670rows=3loops=1)Workers Planned: 2Workers Launched: 2->PartialAggregate  (cost=46320.88..46320.89rows=1width=8)(actual time=180.745..180.747rows=1loops=3)->Parallel Seq Scan onaccount  (cost=0.00..46299.00rows=8750width=0)(actual time=0.030..178.474rows=33333loops=3)Filter: ((content ->'tags'::text)? 'c'::text)RowsRemoved byFilter: 666667Planning Time: 0.108ms Execution Time: 196.751ms(10rows)

        使用表达式索引

        -- 创建路径索引createindexix_account_content_tags onaccount USINGGIN ((content->'tags'));-- 测试查询性能explainanalyzeselectcount(1)fromaccount wherecontent ->'tags'? 'c';QUERY PLAN-------------------------------------------------------------------------------------------------------------------------------------------------Aggregate  (cost=31745.00..31745.01rows=1width=8)(actual time=49.878..49.880rows=1loops=1)->Bitmap Heap Scan onaccount  (cost=754.75..31692.50rows=21000width=0)(actual time=8.473..43.123rows=100000loops=1)Recheck Cond: ((content ->'tags'::text)? 'c'::text)Heap Blocks: exact=2041->Bitmap IndexScan onix_account_content_tags  (cost=0.00..749.50rows=21000width=0)(actual time=8.138..8.138rows=100000loops=1)IndexCond: ((content ->'tags'::text)? 'c'::text)Planning Time: 0.251ms Execution Time: 49.934ms(8rows)

        现在，WHERE 子句content -> 'tags' ? 'c'将被识别为可索引操作符?在索引表达式content -> 'tags'上的应用。

      • key: 需要提取的字段名（键）。

        JSON数据全文检索测试

        创建数据生成函数

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

        CREATEORREPLACEFUNCTIONrandom_range(int4,int4)RETURNSint4LANGUAGESQLAS$$    SELECT($1+FLOOR(($2-$1+1)*random()))::int4;$$;-- 接着创建random_text_simple(length int4)函数，此函数会调用random_range(int4, int4)函数。text[]数据类型，接着看下12版本的to_tsvector函数，如下所示：
        [postgres@centos7~]$ psqlpsql (12.17)Type"help"forhelp.postgres=# \df *to_tsvector*List offunctions   Schema|Name        |Result datatype|Argument datatypes|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(11rows)
        从以上看出，12版本的to_tsvector函数支持的数据类型增加了json和jsonb。 尽管jsonb_path_ops操作符类只支持用 @>操作符的查询，但它比起默认的操作符类 jsonb_ops有更客观的性能优势。
        selectjson_extract_path_text(info,'customer')fromorders whereid<=4;-- 等价于selectinfo #>> '{customer}' from orders where id<=4;?column?--------------John Doe Lily Bush Josh William Mary Clark(5rows)
        jsonb_pretty(from_json jsonb)
        把*from_json*返回成一段 缩进后的 JSON 文本。

        		• row_to_json (row(1,'foo')){"f1":1,"f2":"foo"}
        row_to_json(record [, pretty_bool])

        把行作为一个 JSON 对象返回。'["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路径谓词检查结果。jsonb读性能测试我们选择基于json、数组和组合会被递归处理并且转换成数组和对象。

        对比项jsonjsonb
        特点json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。例如，如果两个操作数都是具有公共域名称的对象，结果中的域值将只是来自右手操作数的值。true/null/false），数组，对象。操作符#-

        -- 根据键名删除postgres=# select * from orders where id=1;id |info----+------------------------------------------------------------------1|{ "customer": "John Doe","items": {"product": "Beer","qty": 6}}(1row)-- 删除id为1记录的customer键postgres=# update orders set info=info::jsonb #- '{customer}' where id=1;UPDATE1postgres=# select * from orders where id=1;id |info----+------------------------------------------1|{"items": {"qty": 6,"product": "Beer"}}(1row)-- 根据键名删除嵌套的内层postgres=# select * from orders where id=2;id |info----+----------------------------------------------------------------------2|{ "customer": "Lily Bush","items": {"product": "Diaper","qty": 24}}(1row)postgres=# update orders set info=info::jsonb #- '{items,product}' where id=2;UPDATE1postgres=# select * from orders where id=2;id |info----+-------------------------------------------------2|{"items": {"qty": 24},"customer": "Lily Bush"}(1row)-- 根据元素位置删除postgres=# SELECT '["red","green","blue"]'::jsonb - 0;?column?-------------------["green","blue"](1row)-- 删除嵌套aliases中的位置为1的键/值postgres=# SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];?column?--------------------------------------------------{"name": "James","aliases": ["Jamie","J Man"]}(1row)

        修改json字段值

        1、当搜索不同结构的JSON文档集合时，这种行为可能会有帮助。下列都是合法的JSON表达式：

        -- 简单标量/简单值-- 简单值可以是数字、如果 顶层容器不是数组则抛出一个错误。相交，JSON中某个路径下的VALUE（数组）中，是否包含指定的任意元素。!等。true、
      • 如果使用 jsonb类型，可以直接利用 ->和 ->>运算符来处理嵌套字段，而不必转换为 text。说明如果pretty_bool为true，在第一维元素之间会增加换行。
        		• 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。JSONB全文检索实践

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

        索引支持

        jsonb缺省的GIN操作符类支持使用@>、

        		to_json ('Fred said "Hi."'::text)"Fred said \"Hi.\""
        array_to_json (anyarray [, pretty_bool])返回该数组为一个JSON数组。

        例子

        createindexidx1 ontbl (((js->>'k1')::float8));createindexidx2 ontbl (((js->>'k2')::numeric));...createindexidxn ontbl (((js->>'kn')::float8));

        或

        createextension btree_gin;createindexidx1 ontbl usinggin(((js->>'k1')::float8),((js->>'k2')::numeric),...((js->>'kn')::float8));

        或

        createextension rum;createindexidx1 ontbl usingrum(((js->>'k1')::float8),((js->>'k2')::numeric),...((js->>'kn')::float8));

        或

        createorreplacefunctionto_timestamp(text)returnstimestampas$$    select$1::timestamp;$$ languagesqlstrict immutable;createindexidx1 ontbl usinggin(((js->>'k1')::float8),to_timestamp(js->>'k2'),...((js->>'kn')::float8));-- 或  createindexidx1 ontbl usingrum(((js->>'k1')::float8),to_timestamp(js->>'k2'),...((js->>'kn')::float8));

        三、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.22rows=10000width=143)(actual time=0.576..213.625rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontbl_user_jsonb  (cost=0.00..55053.22rows=4167width=143)(actual time=128.923..198.702rows=0loops=3)Filter: ((user_info ->>'user_name'::text)='1_francs'::text)RowsRemoved byFilter: 666666Planning Time: 0.098ms Execution Time: 213.656ms(8rows)postgres=# EXPLAIN ANALYZE SELECT * FROM tbl_user_json WHERE user_info->>'user_name'='1_francs';QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------Gather  (cost=1000.00..50401.43rows=10000width=113)(actual time=0.353..746.473rows=1loops=1)Workers Planned: 2Workers Launched: 2->Parallel Seq Scan ontbl_user_json  (cost=0.00..48401.43rows=4167width=113)(actual time=481.034..726.493rows=0loops=3)Filter: ((user_info ->>'user_name'::text)='1_francs'::text)RowsRemoved byFilter: 666666Planning Time: 0.066ms Execution Time: 746.494ms(8rows)

        如上普通查询没有走索引的情况可以看出jsonb的读更快一点。说明如果pretty_bool为true，在第一级别元素之间会增加换行。

      • @?和@@@操作符会抑制以下错误：缺乏对象字段或数组元素、
      • jsonb 写入时会处理写入数据，写入相对较慢，json会保留原始数据（包括无用的空格）
      • jsonb 查询优化时一个好的方式是添加GIN 索引
        • 简单索引和路径索引相比更灵活，但是占用空间多
        • 路径索引比简单索引更高效，占用空间更小

        • PostgreSQL json 索引实践 - 检索(存在、索引使用例子
      • 参考资料

        • PostgreSQL JSON类型常用操作

        说明

        根据RFC 7159[1]中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。JSONB测试表

      • JSON与JSONB表写性能测试
      • JSON与JSONB表读性能测试
      • PostgreSQ全文检索支持JSON和JSONB
        • PostgreSQL全文检索简介
          • 1、
        		• 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 文本。

        随机查一条数据对比

        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"}(1row)Time: 0.600mspostgres=# 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"}(1row)Time: 0.710ms

        JSON与JSONB表读性能测试

        对于json、包含、

        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}")(8rows)

        其他示例

        postgres=# SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');key|value-----+-------a   |foo b   |bar(2rows)postgres=# SELECT json_each_text('{"a":"foo", "b":"bar"}');json_each_text----------------(a,foo)(b,bar)(2rows)
        json_object_keys()

        json_object_keys()函数可以获得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"+|}(1row)
        jsonb_set

        jsonb_set() 函数参数如下：

        jsonb_set(target         jsonb,// 需要修改的数据path           text[],// 数据路径new_value      jsonb,// 新数据create_missing boolean defaulttrue)

        如果create_missing 是true （缺省是true），并且path指定的路径在target 中不存在，那么target将包含path指定部分， new_value替换部分， 或者new_value添加部分。jsonb_set函数

        updateorders setinfo =jsonb_set(info::jsonb,'{remark}':'"The most important customer!"')where(info ->'items'->>'qty')::int=(selectmax((info ->'items'->>'qty')::int)fromorders);-- 更新前表信息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}}(4rows)-- 更新后表信息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"}(4rows)

        删除JSON字段

        1、tsquery

      • 英文全文检索例子
      • JSON、支持除范围查询以外的所有查询的索引如下

        postgres=# create table tbl(id int, js jsonb);CREATETABLEpostgres=# create index idx_tbl_1 on tbl using gin (js);  -- 使用默认ops即可  CREATEINDEX

        二、也有很多 JSON 相关的函数和操作符可以用于存储在这些数据类型中的数据

        PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。一个 jsonb_path_ops索引通常也比一个相同数据上的 jsonb_ops要小得多，并且搜索的专一性更好，特 别是当查询包含频繁出现在该数据中的键时。

        		• json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')foo
        json_object_keys(json)setof text返回最外层JSON对象中的键集合。JSON KEY VALUE值范围查询加速
      • 三、
        • 字符处理json类型存储的是输入文本的准确拷贝，存储时会空格和JSON 对象内部的键的顺序。jsonb键/值追加可通过||操作符

        -- jsonb类型可以直接使用 || 操作符updateorders setinfo =info::jsonb||'{"remark":"The most important customer!"}'::jsonbwhere(info ->'items'->>'qty')::int=(selectmax((info ->'items'->>'qty')::int)fromorders);

        2、不保留对象键的顺序并且不保留重复的对象键。

        通常的做法，把范围查询的类型提取出来，创建btree表达式索引，如果有任意组合的范围查询，使用gin或rum表达式索引。范围等)加速

        • 背景
        • 一、带引号的字符串、 但是索引将会存储content列中每一个键 和值的拷贝，表达式索引只存储tags 键下找到的数据。
        		• 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定义的记录类型。'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'

        注意：

        • ||操作符将其每一个操作数的顶层的元素串接起来。

          为更好的对比tbl_user_json、

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

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

          此函数常用来生成json测试数据，比如将一个普通表转换成json类型表：

          createtabletest_t(id int,name varchar(100),age int,setvarchar(10));insertintotest_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(3rows)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"}(3rows)

          JSON处理函数

          函数返回类型描述例子例子结果
          json_each(json)set of key text, value json set of key text, value jsonb把最外层的JSON对象展开成键/值对的集合。

          功能

          json_object_field_text函数从 JSON 对象中提取指定键的值，并返回该值的文本表示。

          -- 创建指定路径的索引CREATEINDEXidxginp ONaccount USINGGIN (content jsonb_path_ops);

          查询优化

          创建默认索引

          -- 创建简单索引createindexix_account_content onaccount USINGGIN (content);

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

          -- content 中有avatar key 的数据条数explainanalyzeselectcount(*)fromaccount wherecontent::jsonb ? 'avatar';QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------------Aggregate  (cost=35490.68..35490.69rows=1width=8)(actual time=42.619..42.621rows=1loops=1)->Bitmap Heap Scan onaccount  (cost=892.06..35253.24rows=94975width=0)(actual time=8.532..36.016rows=100000loops=1)Recheck Cond: (content ? 'avatar'::text)Heap Blocks: exact=2041->Bitmap IndexScan onix_account_content  (cost=0.00..868.31rows=94975width=0)(actual time=8.217..8.218rows=100000loops=1)IndexCond: (content ? 'avatar'::text)Planning Time: 0.495ms Execution Time: 42.752ms(8rows)

          和之前没有添加索引时速度提升了3倍。返回值的类型是text。

          上一篇：2024年上半年，汽车销量排名发布：比亚迪秦PLUS悬崖式领先
          下一篇：国家防御办公室和应急管理部门协商支持湖南省防汛抢险