HighGoDB—JSON

已于 2024-03-04 09:18:57 修改
阅读量903 收藏 22
点赞数 19
文章标签： 数据库 json
于 2024-03-04 09:15:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39676699/article/details/136443322
版权

HighGoDB—JSON

1.json 和 jsonb 区别

区别：

  • json 类型是将整个json字符进行完整保存，包括空格、重复的键、和键的顺序等。
  • jsonb 类型会对json字符进行解析后保存二进制，解析的时候会删除不必要的空格和重复的键等。

由于上面的区别，所以在储存的时候 json 会比 jsonb 快，毕竟少了一个解析的步骤。但是在查询的时候 json 会比 jsonb 慢。

由于 jsonb 是格式化后的数据，所以他能使用的函数比 json 多很多，jsonb 甚至能使用索引。
注意：这两者在储存的时候，字符串都要符合json规范，要不会储存失败。

2.json 和 jsonb操作符

操作符
描述
例子
json -> integer → json

jsonb -> integer → jsonb

提取JSON数组的第n个元素(数组元素从0开始索引，但负整数从末尾开始计数)。

‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json -> 2 → {“c”:“baz”}

‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json -> -3 → {“a”:“foo”}
json -> text → json

jsonb -> text → jsonb

用给定的键提取JSON对象字段。

‘{“a”: {“b”:“foo”}}’::json -> ‘a’ → {“b”:“foo”}
json ->> integer → text

jsonb ->> integer → text

提取JSON数组的第n个元素，作为text。

‘[1,2,3]’::json ->> 2 → 3
json ->> text → text

jsonb ->> text → text

用给定的键提取JSON对象字段，作为text。

‘{“a”:1,“b”:2}’::json ->> ‘b’ → 2
json #> text[] → json

jsonb #> text[] → jsonb

提取指定路径下的JSON子对象，路径元素可以是字段键或数组索引。

‘{“a”: {“b”: [“foo”,“bar”]}}’::json #> ‘{a,b,1}’ → “bar”
json #>> text[] → text

jsonb #>> text[] → text

将指定路径上的JSON子对象提取为text。

‘{“a”: {“b”: [“foo”,“bar”]}}’::json #>> ‘{a,b,1}’ → bar

3. jsonb额外的操作符

操作符
描述
例子
jsonb @> jsonb → boolean

第一个JSON值是否包含第二个?

‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb → t
jsonb <@ jsonb → boolean

第二个JSON中是否包含第一个JSON值?

‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb → t
jsonb ? text → boolean

文本字符串是否作为JSON值中的顶级键或数组元素存在?

‘{“a”:1, “b”:2}’::jsonb ? ‘b’ → t

‘[“a”, “b”, “c”]’::jsonb ? ‘b’ → t
jsonb ?
jsonb ?& text[] → boolean

文本数组中的所有字符串都作为顶级键或数组元素存在吗?

‘[“a”, “b”, “c”]’::jsonb ?& array[‘a’, ‘b’] → t
jsonb
jsonb - text → jsonb

从JSON对象中删除键(以及它的值)，或从JSON数组中删除匹配的字符串值。

‘{“a”: “b”, “c”: “d”}’::jsonb - ‘a’ → {“c”: “d”}

‘[“a”, “b”, “c”, “b”]’::jsonb - ‘b’ → [“a”, “c”]
jsonb - text[] → jsonb

从左操作数中删除所有匹配的键或数组元素。

‘{“a”: “b”, “c”: “d”}’::jsonb - ‘{a,c}’::text[] → {}
jsonb - integer → jsonb

删除具有指定索引的数组元素(负整数从末尾计数)。如果JSON值不是数组，则抛出错误。

‘[“a”, “b”]’::jsonb - 1 → [“a”]
jsonb #- text[] → jsonb

删除指定路径上的字段或数组元素，路径元素可以是字段键或数组索引。

‘[“a”, {“b”:1}]’::jsonb #- ‘{1,b}’ → [“a”, {}]
jsonb @? jsonpath → boolean

JSON路径是否为指定的JSON值返回任何项?

‘{“a”:[1,2,3,4,5]}’::jsonb @? ‘$.a[*] ? (@ > 2)’ → t
jsonb @@ jsonpath → boolean

返回指定JSON值的JSON路径谓词检查的结果。只考虑结果的第一项。如果结果不是布尔值，则返回NULL。

‘{“a”:[1,2,3,4,5]}’::jsonb @@ ‘$.a[*] > 2’ → t

4.json创建函数

函数
描述
例子
to_json ( anyelement ) → json

to_jsonb ( anyelement ) → jsonb

将任何SQL值转换为json或jsonb。数组和组合递归地转换为数组和对象(多维数组在JSON中变成数组的数组)。 否则，如果存在从SQL数据类型到json的类型转换，则造型函数将用于执行转换; [a] 否则，将生成一个标量json值。对于除数字、布尔值或空值之外的任何标量，将使用文本表示，并根据需要进行转义，使其成为有效的JSON字符串值。

to_json(‘Fred said “Hi.”’::text) → “Fred said \“Hi.\””

to_jsonb(row(42, ‘Fred said “Hi.”’::text)) → {“f1”: 42, “f2”: “Fred said \“Hi.\””}
array_to_json ( anyarray [, boolean ] ) → json

将SQL数组转换为JSON数组。该行为与to_json相同，只是如果可选boolean参数为真，换行符将在顶级数组元素之间添加。

array_to_json(‘{{1,5},{99,100}}’::int[]) → [[1,5],[99,100]]
row_to_json ( record [, boolean ] ) → json

将SQL组合值转换为JSON对象。该行为与to_json相同，只是如果可选boolean参数为真，换行符将在顶级元素之间添加。

row_to_json(row(1,‘foo’)) → {“f1”:1,“f2”:“foo”}
json_build_array ( VARIADIC “any” ) → json

jsonb_build_array ( VARIADIC “any” ) → jsonb

根据可变参数列表构建可能异构类型的JSON数组。每个参数都按照to_json或to_jsonb进行转换。

json_build_array(1, 2, ‘foo’, 4, 5) → [1, 2, “foo”, 4, 5]
json_build_object ( VARIADIC “any” ) → json

jsonb_build_object ( VARIADIC “any” ) → jsonb

根据可变参数列表构建一个JSON对象。按照惯例，参数列表由交替的键和值组成。 关键参数强制转换为文本;值参数按照to_json或to_jsonb进行转换。

json_build_object(‘foo’, 1, 2, row(3,‘bar’)) → {“foo” : 1, “2” : {“f1”:3,“f2”:“bar”}}
json_object ( text[] ) → json

jsonb_object ( text[] ) → jsonb

从文本数组构建JSON对象。该数组必须有两个维度，一个维度的成员数为偶数，在这种情况下，它们被视为交替的键/值对; 另一个维度的成员数为二维，每个内部数组恰好有两个元素，它们被视为键/值对。所有值都转换为JSON字符串。

json_object(‘{a, 1, b, “def”, c, 3.5}’) → {“a” : “1”, “b” : “def”, “c” : “3.5”}

json_object(‘{{a, 1}, {b, “def”}, {c, 3.5}}’) → {“a” : “1”, “b” : “def”, “c” : “3.5”}
json_object ( keys text[], values text[] ) → json

jsonb_object ( keys text[], values text[] ) → jsonb

这种形式的json_object从单独的文本数组中成对地获取键和值。否则，它与单参数形式相同。

json_object(‘{a,b}’, ‘{1,2}’) → {“a”: “1”, “b”: “2”}

5.json处理函数

函数
描述
例子
json_array_elements ( json ) → setof json

jsonb_array_elements ( jsonb ) → setof jsonb

将顶级JSON数组展开为一组JSON值。

select * from json_array_elements(‘[1,true, [2,false]]’) →
value
-----------
1
true
[2,false]
json_array_elements_text ( json ) → setof text

jsonb_array_elements_text ( jsonb ) → setof text

将顶级JSON数组展开为一组文本值。

select * from json_array_elements_text(‘[“foo”, “bar”]’) →
value
-----------
foo
bar
json_array_length ( json ) → integer

jsonb_array_length ( jsonb ) → integer

返回顶级JSON数组中的元素数量。

json_array_length(‘[1,2,3,{“f1”:1,“f2”:[5,6]},4]’) → 5
json_each ( json ) → setof record ( key text, value json )

jsonb_each ( jsonb ) → setof record ( key text, value jsonb )

将顶级JSON对象展开为一组键/值对。

select * from json_each(‘{“a”:“foo”, “b”:“bar”}’) →

` key
json_each_text ( json ) → setof record ( key text, value text )

jsonb_each_text ( jsonb ) → setof record ( key text, value text )

将顶级JSON对象扩展为一组键/值对。返回的值的类型为文本。

select * from json_each_text(‘{“a”:“foo”, “b”:“bar”}’) →

 `key
json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json

jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

在指定路径下提取JSON子对象。(这在功能上相当于#>操作符，但在某些情况下，将路径写成可变参数列表会更方便。)

json_extract_path(‘{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’, ‘f4’, ‘f6’) → “foo”
json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text

jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text

将指定路径上的JSON子对象提取为文本。(这在功能上等同于#>>操作符。)

json_extract_path_text(‘{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’, ‘f4’, ‘f6’) → foo
json_object_keys ( json ) → setof text

jsonb_object_keys ( jsonb ) → setof text

返回顶级JSON对象中的键集合。

select * from json_object_keys(‘{“f1”:“abc”,“f2”:{“f3”:“a”, “f4”:“b”}}’) →

json_object_keys
------------------
f1
f2
json_populate_record ( base anyelement, from_json json ) → anyelement

jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

将顶级JSON对象扩展为具有基本参数的复合类型的行。JSON对象将被扫描，查找名称与输出行类型的列名匹配的字段，并将它们的值插入到输出的这些列中。 (不对应任何输出列名的字段将被忽略。)在典型的使用中，基本的值仅为NULL，这意味着任何不匹配任何对象字段的输出列都将被填充为空。 但是，如果base不为NULL，那么它包含的值将用于不匹配的列。

要将JSON值转换为输出列的SQL类型，需要按次序应用以下规则:


* 在所有情况下，JSON空值都会转换为SQL空值。


* 如果输出列的类型是json或jsonb，则会精确地重制JSON值。


* 如果输出列是复合(行)类型，且JSON值是JSON对象，则该对象的字段将转换为输出行类型的列，通过这些规则的递归应用程序。


* 同样，如果输出列是数组类型，而JSON值是JSON数组，则通过这些规则的递归应用程序将JSON数组的元素转换为输出数组的元素。


* 否则，如果JSON值是字符串，则将字符串的内容提供给输入转换函数，用以确定列的数据类型。


* 否则，JSON值的普通文本表示将被提供给输入转换函数，以确定列的数据类型。

虽然下面的示例使用一个常量JSON值，典型的用法是在查询的FROM子句中从另一个表侧面地引用json或jsonb列。 在FROM子句中编写json_populate_record是一种很好的实践，因为提取的所有列都可以使用，而不需要重复的函数调用。

create type subrowtype as (d int, e text); create type myrowtype as (a int, b text[], c subrowtype);

select * from json_populate_record(null::myrowtype, ‘{“a”: 1, “b”: [“2”, “a b”], “c”: {“d”: 4, “e”: “a b c”}, “x”: “foo”}’) →

` a
json_populate_recordset ( base anyelement, from_json json ) → setof anyelement

jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement

将对象的顶级JSON数组展开为一组具有基本参数的复合类型的行。 对于json[b]_populate_record，将如上所述处理JSON数组的每个元素。

create type twoints as (a int, b int);

select * from json_populate_recordset(null::twoints, ‘[{“a”:1,“b”:2}, {“a”:3,“b”:4}]’) →

` a
json_to_record ( json ) → record

jsonb_to_record ( jsonb ) → record

将顶级JSON对象展开为具有由 AS子句定义的复合类型的行。 (与所有返回record的函数一样，调用查询必须使用AS子句显式定义记录的结构。) 输出记录由JSON对象的字段填充，与上面描述的json[b]_populate_record的方式相同。 由于没有输入记录值，不匹配的列总是用空值填充。

create type myrowtype as (a int, b text);

select * from json_to_record(‘{“a”:1,“b”:[1,2,3],“c”:[1,2,3],“e”:“bar”,“r”: {“a”: 123, “b”: “a b c”}}’) as x(a int, b text, c int[], d text, r myrowtype) →

` a
json_to_recordset ( json ) → setof record

jsonb_to_recordset ( jsonb ) → setof record

将顶级JSON对象数组展开为一组由AS子句定义的复合类型的行。 (与所有返回record的函数一样，调用查询必须使用AS子句显式定义记录的结构。) 对于json[b]_populate_record，将如上所述处理JSON数组的每个元素。

select * from json_to_recordset(‘[{“a”:1,“b”:“foo”}, {“a”:“2”,“c”:“bar”}]’) as x(a int, b text) →

` a
jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb

返回target，将path指定的项替换为new_value， 如果create_if_missing为真(此为默认值)并且path指定的项不存在，则添加new_value。 路径中的所有前面步骤都必须存在，否则将不加改变地返回target。 与面向路径操作符一样，负整数出现在JSON数组末尾的path计数中。 如果最后一个路径步骤是超出范围的数组索引，并且create_if_missing为真，那么如果索引为负，新值将添加到数组的开头，如果索引为正，则添加到数组的结尾。

jsonb_set(‘[{“f1”:1,“f2”:null},2,null,3]’, ‘{0,f1}’, ‘[2,3,4]’, false) → [{“f1”: [2, 3, 4], “f2”: null}, 2, null, 3]

jsonb_set(‘[{“f1”:1,“f2”:null},2]’, ‘{0,f3}’, ‘[2,3,4]’) → [{“f1”: 1, “f2”: null, “f3”: [2, 3, 4]}, 2]
jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb

如果new_value不为NULL，则行为与jsonb_set完全一样。 否则，根据null_value_treatment的值，它必须是’raise_exception’，‘use_json_null’, ‘delete_key’, 或’return_target’。 默认值为’use_json_null’。

jsonb_set_lax(‘[{“f1”:1,“f2”:null},2,null,3]’, ‘{0,f1}’, null) → [{“f1”:null,“f2”:null},2,null,3]

jsonb_set_lax(‘[{“f1”:99,“f2”:null},2]’, ‘{0,f3}’, null, true, ‘return_target’) → [{“f1”: 99, “f2”: null}, 2]
jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb

返回插入new_value的target。 如果path指派的项是一个数组元素，如果 insert_after为假(此为默认值)，则new_value将被插入到该项之前，如果 insert_after为真则在该项之后。 如果由path指派的项是一个对象字段，则只在对象不包含该键时才插入 new_value。 路径中的所有前面步骤都必须存在，否则将不加改变地返回target。 与面向路径操作符一样，负整数出现在JSON数组末尾的 path计数中。 如果最后一个路径步骤是超出范围的数组下标，则如果下标为负，则将新值添加到数组的开头;如果下标为正，则将新值添加到数组的结尾。

jsonb_insert(‘{“a”: [0,1,2]}’, ‘{a, 1}’, ‘“new_value”’) → {“a”: [0, “new_value”, 1, 2]}

jsonb_insert(‘{“a”: [0,1,2]}’, ‘{a, 1}’, ‘“new_value”’, true) → {“a”: [0, 1, “new_value”, 2]}
json_strip_nulls ( json ) → json

jsonb_strip_nulls ( jsonb ) → jsonb

从给定的JSON值中删除所有具有空值的对象字段，递归地。非对象字段的空值是未受影响的。

json_strip_nulls(‘[{“f1”:1, “f2”:null}, 2, null, 3]’) → [{“f1”:1},2,null,3]
jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

检查JSON路径是否返回指定JSON值的任何项。如果指定了vars参数，则它必须是一个JSON对象，并且它的字段提供要替换到jsonpath表达式中的名称值。 如果指定了silent参数并为true，函数会抑制与@? 和 @@运算符相同的错误。

jsonb_path_exists(‘{“a”:[1,2,3,4,5]}’, ‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’) → t
jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

返回指定JSON值的JSON路径谓词检查的结果。只有结果的第一项被考虑在内。 如果结果不是布尔值，则返回NULL。可选的vars和silent参数的作用与jsonb_path_exists相同。

jsonb_path_match(‘{“a”:[1,2,3,4,5]}’, ‘exists($.a[*] ? (@ >= $min && @ <= $max))’, ‘{“min”:2, “max”:4}’) → t
jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

为指定的JSON值返回由JSON路径返回的所有JSON项。可选的vars和silent参数的作用与jsonb_path_exists相同。

select * from jsonb_path_query(‘{“a”:[1,2,3,4,5]}’, ‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’) →

jsonb_path_query
------------------
2
3
4
jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

以JSON数组的形式返回由JSON路径为指定的JSON值返回的所有JSON项。可选的vars和silent参数的作用与jsonb_path_exists相同。

jsonb_path_query_array(‘{“a”:[1,2,3,4,5]}’, ‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’) → [2, 3, 4]
jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

为指定的JSON值返回由JSON路径返回的第一个JSON项。如果没有结果则返回NULL。 可选的vars和silent参数的作用与 jsonb_path_exists相同。

jsonb_path_query_first(‘{“a”:[1,2,3,4,5]}’, ‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’) → 2
jsonb_path_exists_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

jsonb_path_match_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

jsonb_path_query_array_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

jsonb_path_query_first_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

这些函数与上面描述的没有_tz后缀的对应函数类似，除了这些函数支持需要时区感知转换的日期/时间值比较之外。 下面的示例需要将只包含日期的值2015-08-02解释为带有时区的时间戳，因此结果依赖于当前TimeZone设置。 由于这种依赖性，这些函数被标记为稳定的，这意味着不能在索引中使用这些函数。 它们的对应项是不可改变的，因此可以用于索引;但是，如果要求他们进行这样的比较，他们就会抛出错误。

jsonb_path_exists_tz(‘[“2015-08-01 12:00:00 -05”]’, ‘$[*] ? (@.datetime() < “2015-08-02”.datetime())’) → t
jsonb_pretty ( jsonb ) → text

将给定的JSON值转换为精美打印的，缩进的文本。

jsonb_pretty(‘[{“f1”:1,“f2”:null}, 2]’) →

[
{
"f1": 1,
"f2": null
},
2
]
json_typeof ( json ) → text

jsonb_typeof ( jsonb ) → text

以文本字符串形式返回顶级JSON值的类型。可能的类型有object, array,string, number,boolean, 和 null。 (null的结果不应该与SQL NULL 混淆;参见示例。)

json_typeof(‘-123.4’) → number

json_typeof(‘null’::json) → null

json_typeof(NULL::json) IS NULL → t

6.JSON 和 JSONB 测试

创建一个 json_test 表 ,json_data 是 json 类型,jsonb_data 是 jsonb 类型

CREATE TABLE json_test (
  id serial PRIMARY KEY,
  json_data json,
  jsonb_data jsonb
);

向表中插入数据

-- 这里注意一下两个 json 对象都有一个相同的键'name'。

INSERT INTO 
	json_test(json_data,jsonb_data)
VALUES(
	'{
            "name":"zhangsan",
            "name":"zhangsan",
            "score":{"yuwen":98,"shuxue":97}            	
	}'::json,
	'{
            "name":"lisi",
            "name":"lisi1",
            "score":{"yuwen":95,"shuxue":96}	
	}'::jsonb
);

查询写入的数据,查询结果中 json_data 保留了空格等字符， jsonb_data 中只保留了一个 name 并且键的顺序也改变了 。

highgo=# SELECT * FROM json_test ;
 id |            json_data                         |                       jsonb_data                        
----+----------------------------------------------+---------------------------------------------------------
  1 | {                                           +| {"name": "lisi1", "score": {"yuwen": 95, "shuxue": 96}}
    |     "name":"zhangsan",                      +| 
    |     "name":"zhangsan",                      +| 
    |     "score":{"yuwen":98,"shuxue":97}        +| 
    | }                                            | 
(1 行记录)

7.jsonb增删改

7.1.添加元素

使用 jsonb_set ，添加 “address”: [“山东”, “济南”]。

UPDATE 
  json_test
SET 
  jsonb_data = jsonb_set(jsonb_data, '{address}', '["山东","济南"]')
WHERE 
  id = 1;

highgo=# SELECT id,jsonb_data FROM json_test where id=1;
 id |                                      jsonb_data                                      
----+--------------------------------------------------------------------------------------
  1 | {"name": "lisi1", "score": {"yuwen": 95, "shuxue": 96}, "address": ["山东", "济南"]}
(1 行记录)

除了使用上面的 jsonb_set 也可以使用 || 操作符。

UPDATE 
  json_test
SET 
  jsonb_data = jsonb_data || '{"age":18}'
WHERE 
  id = 1;

highgo=# SELECT id,jsonb_data FROM json_test where id=1;
 id |                                           jsonb_data                                            
----+-------------------------------------------------------------------------------------------------
  1 | {"age": 18, "name": "lisi1", "score": {"yuwen": 95, "shuxue": 96}, "address": ["山东", "济南"]}
(1 行记录)
7.2.更新元素

使用jsonb_set，将"name": "lisi1"更新为lisi

UPDATE 
  json_test
SET 
  jsonb_data = jsonb_set(jsonb_data, '{name}', '"lisi"')
WHERE 
  id = 1;

highgo=# SELECT id,jsonb_data FROM json_test where id=1;
 id |                                           jsonb_data                                           
----+------------------------------------------------------------------------------------------------
  1 | {"age": 18, "name": "lisi", "score": {"yuwen": 95, "shuxue": 96}, "address": ["山东", "济南"]}
(1 行记录)

使用 || 操作符，将 “age”:18 更新为 20

UPDATE 
  json_test
SET 
  jsonb_data = jsonb_data || '{"age":20}'
WHERE 
  id = 1;

highgo=# SELECT id,jsonb_data FROM json_test where id=1;
 id |                                           jsonb_data                                           
----+------------------------------------------------------------------------------------------------
  1 | {"age": 20, "name": "lisi", "score": {"yuwen": 95, "shuxue": 96}, "address": ["山东", "济南"]}
(1 行记录)

更新嵌套元素，使用jsonb_set,更新score->yuwen为100

UPDATE 
  json_test
SET 
  jsonb_data = jsonb_set(jsonb_data, '{score,yuwen}', '100')
WHERE 
  id = 1;

highgo=# SELECT id,jsonb_data FROM json_test where id=1;
 id |                                           jsonb_data                                            
----+-------------------------------------------------------------------------------------------------
  1 | {"age": 20, "name": "lisi", "score": {"yuwen": 100, "shuxue": 96}, "address": ["山东", "济南"]}
(1 行记录)
7.3.删除元素

删除age

UPDATE 
  json_test
SET 
  jsonb_data = jsonb_data - 'age'
WHERE 
  id = 1;

highgo=# SELECT id,jsonb_data FROM json_test where id=1;
 id |                                      jsonb_data                                      
----+--------------------------------------------------------------------------------------
  1 | {"name": "lisi", "score": {"yuwen": 100, "shuxue": 96}, "address": ["山东", "济南"]}
(1 行记录)

8.jsonb查询

初始化数据

highgo=# INSERT INTO json_test ( jsonb_data ) select ( '{"id":"' || sys_guid () || '", "content":"世界的PG"}' ):: jsonb FROM generate_series ( 1, 2000000 );
INSERT 0 2000000

highgo=# INSERT INTO json_test ( jsonb_data ) select ( '{"id":"' || sys_guid () || '", "content":"中国的瀚高"}' )::jsonb from generate_series(1,10000);
INSERT 0 10000

查询一：包含 {“content”: “中国的瀚高”}’

全表扫描

highgo=# explain analyze select jsonb_data->>'id',jsonb_data from json_test where jsonb_data @> '{"content": "中国的瀚高"}';
                                                    QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..40402.01 rows=2011 width=105) (actual time=515.011..527.282 rows=10000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on json_test  (cost=0.00..39200.91 rows=838 width=105) (actual time=491.039..492.609 rows=3333 loops=3)
         Filter: (jsonb_data @> '{"content": "中国的瀚高"}'::jsonb)
         Rows Removed by Filter: 666667
 Planning Time: 0.056 ms
 Execution Time: 527.669 ms
(8 行记录)

jsonb_data字段创建gin索引后，可走索引

highgo=# create index on json_test using gin(jsonb_data);
CREATE INDEX
highgo=# explain analyze select jsonb_data->>'id',jsonb_data from json_test where jsonb_data @> '{"content": "中国的瀚高"}';
                                                   QUERY PLAN                                                                
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on json_test  (cost=51.58..6337.75 rows=2010 width=105) (actual time=0.906..4.786 rows=10000 loops=1)
   Recheck Cond: (jsonb_data @> '{"content": "中国的瀚高"}'::jsonb)
   Heap Blocks: exact=155
   ->  Bitmap Index Scan on json_test_jsonb_data_idx  (cost=0.00..51.08 rows=2010 width=0) (actual time=0.882..0.882 rows=10000 loops=1)
         Index Cond: (jsonb_data @> '{"content": "中国的瀚高"}'::jsonb)
 Planning Time: 1.367 ms
 Execution Time: 5.270 ms
(7 行记录)

查询二：包含: ‘中国的瀚高’

全表扫描

highgo=# explain analyze select jsonb_data->>'id',jsonb_data from json_test where jsonb_data -> 'content' ? '中国的瀚高';
                                                   QUERY PLAN                                                             
----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..42491.60 rows=2010 width=105) (actual time=1269.887..1286.396 rows=10000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on json_test  (cost=0.00..41290.60 rows=838 width=105) (actual time=1238.014..1241.249 rows=3333 loops=3)
         Filter: ((jsonb_data -> 'content'::text) ? '中国的瀚高'::text)
         Rows Removed by Filter: 666667
 Planning Time: 1.184 ms
 Execution Time: 1287.442 ms
(8 行记录)

对 jsonb_data 字段的 content 元素创建gin索引。 可走索引

highgo=# create index on json_test using gin((jsonb_data->'content'));
CREATE INDEX
highgo=# explain analyze select jsonb_data->>'id',jsonb_data from json_test where jsonb_data -> 'content' ? '中国的瀚高';
                                                 QUERY PLAN                                                             
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on json_test  (cost=375.58..6666.77 rows=2010 width=105) (actual time=0.963..5.063 rows=10000 loops=1)
   Recheck Cond: ((jsonb_data -> 'content'::text) ? '中国的瀚高'::text)
   Heap Blocks: exact=155
   ->  Bitmap Index Scan on json_test_expr_idx  (cost=0.00..375.08 rows=2010 width=0) (actual time=0.937..0.937 rows=10000 loops=1)
         Index Cond: ((jsonb_data -> 'content'::text) ? '中国的瀚高'::text)
 Planning Time: 0.523 ms
 Execution Time: 5.413 ms
(7 行记录)

查询三：获取 {“content”: “中国的瀚高”}’

全表扫描

highgo=# explain analyze select * from json_test where jsonb_data->>'content' = '中国的瀚高';
                                                  QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..43293.51 rows=10050 width=109) (actual time=430.660..438.629 rows=10000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on json_test  (cost=0.00..41288.51 rows=4188 width=109) (actual time=412.828..413.549 rows=3333 loops=3)
         Filter: ((jsonb_data ->> 'content'::text) = '中国的瀚高'::text)
         Rows Removed by Filter: 666667
 Planning Time: 0.103 ms
 Execution Time: 438.945 ms
(8 行记录)

针对这类查询，jsonb_data->>‘content’ 返回类型为text，那么可以考虑创建一个btree索引，也可以走索引

highgo=# create index on json_test using btree((jsonb_data->>'content'));
CREATE INDEX
highgo=# explain analyze select * from json_test where jsonb_data->>'content' = '中国的瀚高';
                                                 QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on json_test  (cost=234.31..20597.53 rows=10050 width=109) (actual time=7.324..8.114 rows=10000 loops=1)
   Recheck Cond: ((jsonb_data ->> 'content'::text) = '中国的瀚高'::text)
   Heap Blocks: exact=155
   ->  Bitmap Index Scan on json_test_expr_idx1  (cost=0.00..231.80 rows=10050 width=0) (actual time=7.304..7.304 rows=10000 loops=1)
         Index Cond: ((jsonb_data ->> 'content'::text) = '中国的瀚高'::text)
 Planning Time: 2.955 ms
 Execution Time: 8.643 ms
(7 行记录)

由于 jsonb_data->>‘content’ 返回为text类型，所以可在其上面做许多操作，比如in，exists等

highgo=# explain analyze select * from json_test where jsonb_data->>'content' in ('中国的瀚高','000');
                                                QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on json_test  (cost=468.63..28170.17 rows=20100 width=109) (actual time=0.946..2.631 rows=10000 loops=1)
   Recheck Cond: ((jsonb_data ->> 'content'::text) = ANY ('{中国的瀚高,000}'::text[]))
   Heap Blocks: exact=155
   ->  Bitmap Index Scan on json_test_expr_idx1  (cost=0.00..463.61 rows=20100 width=0) (actual time=0.916..0.917 rows=10000 loops=1)
         Index Cond: ((jsonb_data ->> 'content'::text) = ANY ('{中国的瀚高,000}'::text[]))
 Planning Time: 0.173 ms
 Execution Time: 3.217 ms
(7 行记录)

三种查询都能得到相同的结果，可以看出第一种针对于jsonb字段的gin索引，适用于jsonb字段所有的元素，而第二种和第三种分别是对单个元素创建的gin和btree索引。

等值查询方面可能单个元素的btree索引占用空间小，且效率较高，如果单独某个元素的查询较为频繁可选择btree索引，而整个jsonb创建gin对所有元素有效。

第一种传入的是一个json，而第二种，第三种传入的是字符串。

9.jsonb元素值模糊查询

在前面只有jsonb_data gin索引情况下，like全模糊匹配不能走索引

highgo=# explain analyze select * from json_test where jsonb_data->>'content' like '%世界的PG%';
                                                QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..42308.61 rows=201 width=109) (actual time=6.793..5605.215 rows=2000000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on json_test  (cost=0.00..41288.51 rows=84 width=109) (actual time=1.975..2229.228 rows=666667 loops=3)
         Filter: ((jsonb_data ->> 'content'::text) ~~ '%世界的PG%'::text)
         Rows Removed by Filter: 3334
 Planning Time: 2.361 ms
 Execution Time: 5658.811 ms
(8 行记录)

由于 jsonb_data->>‘content’ 返回的是text类型，所以考虑再其上面使用pg_trgm,创建gin索引。

highgo=# create index on json_test using gin((jsonb_data->>'content') gin_trgm_ops);
CREATE INDEX
highgo=# explain analyze select * from json_test where jsonb_data->>'content' like '%世界的PG%';
                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on json_test  (cost=1025.56..1782.13 rows=201 width=109) (actual time=181.867..561.093 rows=2000000 loops=1)
   Recheck Cond: ((jsonb_data ->> 'content'::text) ~~ '%世界的PG%'::text)
   Heap Blocks: exact=28572
   ->  Bitmap Index Scan on json_test_expr_idx2  (cost=0.00..1025.51 rows=201 width=0) (actual time=177.988..177.989 rows=2000000 loops=1)
         Index Cond: ((jsonb_data ->> 'content'::text) ~~ '%世界的PG%'::text)
 Planning Time: 0.185 ms
 Execution Time: 601.443 ms
(7 行记录)

还有一种方式就是将该jsonb字段转为text，然后再创建gin索引,但是这样的模糊匹配，可能匹配到其他元素中包含同样的值，所以需要加上辅助条件：jsonb_data->>‘content’ like ‘%世界的PG%’，用来确保是该元素

highgo=# create index on json_test using gin ((jsonb_data::text) gin_trgm_ops);
CREATE INDEX
highgo=# explain analyze select * from json_test where jsonb_data->>'content' like '%世界的PG%' and jsonb_data::text like '%世界的PG%';
                                                 QUERY PLAN                                                                   
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on json_test  (cost=45.51..803.59 rows=1 width=109) (actual time=192.414..1486.643 rows=2000000 loops=1)
   Recheck Cond: ((jsonb_data)::text ~~ '%世界的PG%'::text)
   Filter: ((jsonb_data ->> 'content'::text) ~~ '%世界的PG%'::text)
   Heap Blocks: exact=28572
   ->  Bitmap Index Scan on json_test_jsonb_data_idx1  (cost=0.00..45.51 rows=201 width=0) (actual time=188.348..188.349 rows=2000000 loops=1)
         Index Cond: ((jsonb_data)::text ~~ '%世界的PG%'::text)
 Planning Time: 4.261 ms
 Execution Time: 1529.612 ms
(8 行记录)

第二种方法效率相对于第一种要低一点，但是所有元素都可使用

10.结语

  • 在json和jsonb选择上，json更加适合用于存储，jsonb更加适用于检索。
  • 可以对整个jsonb字段创建gin索引，同时也可以对jsonb中某个元素创建gin索引，或者btree。btree效率最高。
  • (jsonb_data->>‘content’)返回的是一个text类型，所以可以在该属性上创建对应类型的索引，比如btree，gin索引。
  • 对于元素值的模糊匹配可以创建单个元素的gin索引，也可以创建整个jsonb字段的gin索引，前者效率较高，后者适用所有元素。
奔跑的小十一
关注
  • 19
    点赞
  • 踩
  • 22
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值