认识PostgreSQL中Hstore、Json(b)数据类型
2019-08-24 14:17:31 
阿炯
这两种类型为PG中对NoSQL(hstore, json, and jsonb)类型的支持。hstore是一个扩展模块，它允许你保存key=>value键值对，且键值都只能是text类型(但是，值也允许sql的NULL)。
json与jsonb 允许你保存一个有效的json值(定义)。例如，以下都是有效的json表示方式: null, true, [1, false, "string", {"foo":"bar"}], {"foo":"bar", "baz":[null]}.
相比json，hstore只是它的一个很小的子集(但如果需求就是这个子集的话也是相当不错的)。
Hstore是按键值对的形式直接存储在数据库中。它的优点是不需要提前定义键，直接插入要保存的内容即可，且可以在键上加索引，缺点是不支持嵌套对象存储。可以看出hstore足够的灵活性，特别是再加上postgres独有的GIN，GiST索引。
Hstore arrived way back in Postgres 8.3, before upsert, before streaming replication, and before window functions. Hstore is essentially a key/value store directly in Postgres. With hstore you're a little more limited in terms of the datatypes you have: you essentially just get strings. You also don't get any nesting; in short it's a flat key/value datatype.
但是它有两大局限：
1.它只处理文本。
2.它不支持全文档存储意味着你不能嵌套对象。
但是hstore主要特性包括可以在其上使用索引，众多操作符强有力支持，显而易见的，对数据的扩展性不够。一些基本的操作符包括：
返回列foo中关键字bar的值:
foo->"bar"
指定的列foo是否包含了关键字bar:
foo?"bar"
指定的列foo是否包含了键值对bar->baz:
foo@>"bar->baz"
JSON是直接的文本存储，所以可以嵌套对象，但是不可以使用索引。JSON在Postgres 9.3中得到更好的支持，有一些自带的操作符。
Postgres 9.2 arrived it was well received as the JSON release.Finally in Postgres 9.4 we got real and proper JSON in the form of JSONB. The B stands for better. JSONB is a binary representation of JSON, this means it's compressed and more efficient for storage than just text. It also has a similar plumbing of hstore underneath. In fact, once upon a time there was almost hstore2 and a separate JSON type, but the two converged into the JSONB we have today.
JSON中可以存储有效的JSON值，例如null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]}这些都可以存入json中。官方文档上对此有说明：
有两个JSON数据类型：json和jsonb。它们接受几乎相同的值组作为输入。它们实际的主要差别是效率。json 数据类型存储输入文本的精确拷贝，处理函数必须在每个执行上重新解析；而jsonb数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析。jsonb也支持索引，这也是一个明显的优势。
因为json类型存储输入文本的精确拷贝，它将保存令牌间语义上无关紧要的空格，和JSON对象中键的顺序。另外，如果值中的一个JSON对象多次包含相同的键，那么保存所有的键/值对。(处理函数将最后一个值当做操作值)相比之下，jsonb不保存空格，也不保存对象键的顺序，并且不保存重复对象键。如果在输入中指定了重复的键，那么只保存最后一个值。下面对比一下它们：
json与jsonb的区别主要是它们的存储方式：
json是保存为文本格式的
jsonb是保存为二进制格式的
这主要有三方面的影响：
jsonb通常比json占用更多的磁盘空间(有些情况不是)
jsonb比json的写入更耗时间
json的操作比jsonb的操作明显更耗时间(在操作一个json类型值时需要每次都去解析)
当jsonb将在未来稳定版发行可用时，这有两个主要使用情况，你很容易在他们之间选择的：
如果你的应用只用json表示，PostgreSQL只用于保存与获取时，你应该使用json。
如果你需要在PostgreSQL中做比较多的json值的操作，或者在一些json字段上使用索引时，你应该使用jsonb。
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
JSONB
站在用户的角度来说JSONB和JSON的操作方式几乎相同，都接受相同值作为输入。它们二者之间主要的区别是效率。
首先，JSON 数据类型存储输入文本的精确拷贝，处理函数必须在每个执行上重新解析，而JSONB数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析(JSONB格式要比JSON格式多占用空间)。其次，JSONB支持索引，这亦是一个明显的优势。jsonb缺省的GIN操作符类支持使用@>、?、?&和?|操作符查询。
--------------------------------------------------------------
Hstore 数据类型使用介绍
简单的说 hstore 数据类型用来存储具有多个属性值的数据，如 key => value ， key 代表存储的键， values 为相应属性对应的值。
安装该模块
# create extension hstore;
For Rpm:
yum install postgresql-contrib
https://www.postgresql.org/docs/current/static/hstore.html
http://www.postgres.cn/docs/10/hstore.html
相关的操作及函数
查看包含了相应键的记录
select * from sys_partion where partions ? '/exdsk';
查看包含了相应键值记录对的记录
select * from sys_partion where partions @> '"/exdsk"=>"12"' :: hstore;
查看包含了所有键的记录
select * from sys_partion where partions ?& ARRAY[ '/home', '/exdsk' ];
查看包含了任一键的记录
select * from sys_partion where partions ?| ARRAY[ '/home', '/exdsk' ];
转json(b)是非常容易的
select hstore_to_json(partions) from sys_partion limit 9;
将所有的键值对转集合后输出
select *,(each(partions)).* from sys_partion limit 9;
-------------------------------
CREATE TABLE books (
id serial primary key,
title VARCHAR (255),
attr hstore
);
INSERT INTO books (title, attr)
VALUES(
'PostgreSQL Tutorial',
'"paperback" => "243",
"publisher" => "www.postgresql.org",
"language" => "English",
"ISBN-13" => "978-1449370000",
"weight" => "11.2 ounces"'
);
INSERT INTO books (title, attr)
VALUES (
'PostgreSQL Cheat Sheet',
'"paperback" => "9",
"publisher" => "www.freeoa.net",
"language" => "Chinese",
"ISBN-13" => "978-1449370001",
"weight" => "1 ounces"'
);
可以根据不同的键或值进行查询
SELECT attr -> 'ISBN-13' AS isbn FROM books;
SELECT attr -> 'weight' AS weight FROM books WHERE attr -> 'ISBN-13' = '978-1449370000';
select attr->'author' as author from books where attr->'language' = 'Chinese';
为hstore字段增加k/v，如果存在则更新：
update htest set h = h || ('foo'=>'bar') where t='key';
UPDATE books SET attr = attr || '"freeshipping"=>"yes"' :: hstore;
update htest set h = coalesce(h, hstore(array[]::varchar[])) || ('foo'=>'bar') where t='key';
For Postgres version > 9.1:
UPDATE htest SET h = COALESCE(h, hstore('')) || hstore('foo', 'bar') WHERE t='key';
UPDATE books SET attributes = hstore(ARRAY[['pages', '570'],['category','gis'],['includes','code samples'],['media', 'ebook']]) WHERE id = 10;
从中删除指定键
UPDATE sys_partion SET partions = delete(partions, '/exdsk') where ipnum=181278535 and creatime='2019-08-22 11:16:02';
UPDATE books SET attr = delete(attr, 'freeshipping');
从hstore类型列中取得所有的键
To get all keys from an hstore column, you use the akeys() function as follows:
SELECT akeys(attr) FROM books;
Or you can use the skey() function if you want PostgreSQL to return the result as a set.
SELECT skeys(attr) FROM books;
Get all values from an hstore column
Like keys, you can get all values from an hstore column using the avals() function in the form of arrays.
SELECT avals (attr) FROM books;
Or you can use the svals() function if you want to get the result as a set.
SELECT svals(attr) FROM books;
Convert hstore data to JSON
PostgreSQL provides the hstore_to_json() function to convert hstore data to JSON:
SELECT title,hstore_to_json(attr) json FROM books;
Convert hstore data to sets
To convert hstore data to sets, you use the each() function as follows:
SELECT title, (EACH(attr)).* FROM books;
--------------------------------------------------------------
JSON 数据类型使用介绍
JSON stands for JavaScript Object Notation. JSON is an open standard format that consists of key-value pairs. The main usage of JSON is to transport data between a server and web application. Unlike other formats, JSON is human-readable text.
JSON 数据类型是用来存储 JSON(JavaScript Object Notation) 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。
PostgreSQL supports native JSON data type since version 9.2. It provides many functions and operators for manipulating JSON data.
JSON Types
http://www.postgres.cn/docs/10/datatype-json.html
https://www.postgresql.org/docs/current/static/datatype-json.html
JSON Functions and Operators
http://www.postgres.cn/docs/10/functions-json.html
https://www.postgresql.org/docs/current/static/functions-json.html
Postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率：json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等；而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同，使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。
注意：键值对的键必须使用双引号。
数据的类型转换
to cast the json value to text and then to float.
(json_data #>> '{field}')::float
(json_data->'position'->'lat')::text::float cast
查hdfs每天每类型的空间占用量
SELECT AVG (( hj.hdnn_fsn ->> 'CapacityUsedGB' ) :: INT
) :: INT AS used_gb,
hin.hbname,
hj.cdt :: DATE AS dt
FROM
hdbjmx hj,hbidname hin
WHERE
hj.hbid=hin.hbid and
hj.htype = 'nn'
AND hj.cdt BETWEEN '2017-11-20 00:00:00' AND '2017-11-26 23:59:59'
GROUP BY
hin.hbname,dt
CREATE TABLE orders (
ID serial NOT NULL PRIMARY KEY,
info json NOT NULL
);
The orders table consists of two columns:
The id column is the primary key column that identifies the order.
The info column stores the data in the form of JSON.
INSERT INTO orders (info)
VALUES('{ "customer": "John 郑", "items": {"product": "Beer","qty": 6}}');
INSERT INTO orders (info) VALUES
('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 11}}'),
('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');
PG提供了两种原生的访问操作符来访问JSON数据:->,->>
PostgreSQL provides two native operators -> and ->> to help you query JSON data.There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text.
The operator -> returns JSON object field by key.
The operator ->> returns JSON object field by text.
数据类型转换
CAST (info -> 'items' ->> 'qty' AS INTEGER)
PostgreSQL JSON 函数
json(b) 的常用函数及操作符
下面是对JSON格式操作的一些例子：
--获取JSON数组元素(索引从0开始)
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;
--获取JSON对象
select '{"a": {"b":"foo"}}'::json->'a';
SELECT '[1,2,3]'::json->>2;
--获取JSON对象的文本(as text)
select '{"a":1,"b":2}'::json->>'b';
--根据路径获取JSON对象
select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';
--根据路径获取JSON对象的文本
select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
--指定的路径获取JSON对象#>，右操作符为: text[]
SELECT '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';
即在获取a.b的值
--指定的路径获取JSON对象为text，#>>右操作符为: text[]
SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
即获取a[2]的值并转为text
再补充一个实际中的例子：
CREATE TABLE json_test (
id serial PRIMARY KEY,
attributes json
);
INSERT INTO json_test (attributes)
VALUES ( '{"hello": "hello-value",
"world": {"world_type": "earth","world_value": "world"}
}');
--这里的俩条查询语句我之所以用 #>>这个操作符，因为等号右边是文本
select * from json_test
where attributes #>> '{hello}' = 'hello-value';
select * from json_test
where attributes #>> '{world,world_type}' = 'earth';
除了上述的->，->>， #>， #?这些操作符外，还有很多操作符以及函数，可以说是相当丰富了。
jsonb 操作符
@>
右操作数的类型： jsonb, 左侧的JSONB的是否包含右侧的
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;
<@
右操作数的类型： jsonb, 右侧的JSONB的是否包含左侧的
SELECT '{"a":1, "b":2}'::jsonb <@ '{"b":2}'::jsonb;
SELECT '{"a":1, "b":2}'::jsonb <@ '{"b":2,"c":3,"a":1}'::jsonb;
?
右操作符: text, 该字符串是否存在于json的顶级key中
SELECT '{"a":1, "b":2}'::jsonb ? 'b';
?|
右操作符：text[]，这些元素之一是否存在于json的顶级key中
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'cee', 'e'];
?&
右操作符：text[]，所有这些元素是否存都在于json的顶级key中
SELECT '["a", "b"]'::jsonb ?& array['a', 'b'];
SELECT '["a", "b"]'::jsonb ?& array['a', 'b', 'c'];
||
右操作符: jsonb, 拼接两个jsonb生成一个新的jsonb
SELECT '["a", "b", {"hello":"world"}]'::jsonb || '["c", "d", {"hello":"world"}]'::jsonb;
-
右操作符：text，从左操作数中删除K/V或者字符串元素
SELECT '{"a": "b","c":[1,5,9]}'::jsonb - 'c';
右操作符：int， 删除指定索引的元素(负数表示从结尾开始)
SELECT '["a", "b"]'::jsonb - (-1);
SELECT '["a", "b"]'::jsonb - 1;
#-
右操作符： text[]， 删除字段或指定路径的元素
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';
-----------
["a", {}]
SELECT '["a", {"b":1},{"c":2,"f":5}]'::jsonb #- '{2,c}';
---------------------------
["a", {"b": 1}, {"f": 5}]
SELECT '["a", {"b":1}]'::jsonb #- '{0}';
------------
[{"b": 1}]
常用json函数
json_each() function allows us to expand the outermost JSON object into a set of key-value pairs,get a set of key-value pairs as text, you use the json_each_text().
json_object_keys() get a set of keys in the outermost JSON object.
json_typeof() function returns type of the outermost JSON value as a string. It can be number, boolean, null, object, array, and string.
row_to_json,array_to_json
select row_to_json(table_name) from table_name;
select array_to_json(array_agg(province)) from province;
select array_to_json(array_agg(row_to_json(province))) from province;
json(b)中的CRUD
# \d+ sp2
数据表 "monsys.sp2"
栏位 | 类型 | 修饰词 | 存储 | 统计目标 | 描述
----------+-----------------------------+--------------------------------------------------------------+----------+----------+------
ipnum | bigint | 非空 默认 '2130706433'::bigint | plain | |
creatime | timestamp without time zone | 非空 默认 '2019-01-01 00:00:00'::timestamp without time zone | plain | |
partions | json
添加jsonb的字段
select * from sp2 where ipnum=181278536 and creatime='2019-08-13 18:19:26';
--为partions添加一个'/home'分区，并为其设定一个值。
UPDATE sp2 set partions = jsonb_set(partions, '{/home}', '"95"', true) where ipnum=181278536 and creatime='2019-08-13 18:19:26';
--修改已经存的键的值
UPDATE sp2 set partions = jsonb_set(partions, '{/boot}', '"99"'::text::jsonb, true) where ipnum=181278536 and creatime='2019-08-13 18:19:26';
UPDATE sp2 set partions = jsonb_set(partions, '{/boot}', '"90"'::jsonb, true) where ipnum=181278536 and creatime='2019-08-13 18:19:26';
增加或修改键(key)
UPDATE dbs set attr = attr || '{"islave":"1","Seconds_Behind_Master":"1000"}' where ipaddr='...' returning *;
删除jsonb的某字段
--将'/boot'键从中移除
UPDATE sp2 set partions = (partions - '/boot') where ipnum=181278536 and creatime='2019-08-13 18:19:26';
移除其中的键(key)
UPDATE dbs SET attr = attr::jsonb - '{islave,Seconds_Behind_Master}'::text[] where ipaddr='...' returning *;
Jsonb操作手记
当为表中属性字段(attr)记录设置一个认证密码的key属性时
UPDATE dbs set attr = attr || '{"authpwd":"zjLFreeOAx"}' where id in (109);
UPDATE dbs set attr = jsonb_set(attr, '{authpwd}', '"zw123x"') where id in (111,156);
发现上面自redis操作没有成功，是因为attr字段为全空，而非json中的空('{}')，因此上面的update会成功但结果为空(没有)。
因此将属性字段重置为json中的空'{}'(注意：是空字段NULL)
UPDATE dbs set attr = '{}' where attr is null;
UPDATE dbs set attr = jsonb_set(attr, '{authpwd}', '"vgFreeOAei"', true) where assetsid=103 and dbcat='mysql';
在PostgreSQL中的json类型字段检查是否存在相关的key字段
use ->:
where (json->'attribute') is not null
查找103区域的从库信息
select id,ipaddr,dbcat,attr from dbs where assetsid=103 and (attr->'islave') is not null;
可以使用json类型专用的'?'操作符
WHERE column_name::jsonb ? 'attribute'
NOTE: Only for jsonb type.
select id,ipaddr,dbcat,attr from dbs where assetsid=103 and attr?'islave';
PgSQL的不同版本对json类型的数据操作上略有不同，请在操作时注意数据库服务器的版本。下面再举一示例来体现：
create table jsbt2(jid int2 not null,attr jsonb);
insert into jsbt2 values(11,'{"A":"B"}');
insert into jsbt2 values(12,'{"X":"FreeOA"}');
将json对象中的"A"的值更改为当前时间的预操作
SELECT jsonb_set('{"A":"B"}', '{"A"}', to_jsonb(now()));
对attr这个json对象中的字段进行值的更新
UPDATE jsbt2 SET attr = jsonb_set('{"A":"B"}', '{"A"}', to_jsonb(now())) where jid=11;
对attr这个json对象中的字段新增key并进行值的更新
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', to_jsonb(now())) where jid=11 returning *;
对attr这个json字段中的两key的值对比
SELECT (attr #>>'{A}') < (attr #>>'{X}') FROM jsbt2 where jid=11;
执行失败
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X, Lone}', '"Love"') where jid=12 returning *;
错误: 22P02: 位置 2 的路径元素不是一个整数："Lone"
为其中的json key加入数组元素
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X,1}', '"Love"') where jid=12 returning *;
12 | {"X": ["Love"]}
为符合条件的记录加上X这个key，有的话将其置为空数组([])
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', '[]') where;
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', '10') where jid=13;
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', '[10,13]') where jid=13 returning *;
刪掉json中key下面的某键数组中的值：
UPDATE jsbt2 SET attr = attr #- '{X, 1}' where jid=13 returning *;
13 | {"X": [10]}
会将数组中的'13'这个数组值移除掉。
添加键与值
To add fields and values in jsonb, deep object structures, or arrays, we must refer back to JSON Processing Functions article. For many of the operations we use the jsonb_set function which is defined like:
jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])
It returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist.
Full parameter list of jsonb_set():
jsonb_set(target jsonb,
path text[],
new_value jsonb,
create_missing boolean default true)
path can contain JSON array indexes too & negative integers that appear there count from the end of JSON arrays.
insert into jsbt2 values(15,'{"Age":37,"No":7}');
Add field
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Age}', '"36"', TRUE) where jid=12 returning *;
12 | {"No": 7, "Age": "36"}
Add array
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Phones}', '[138003,139007]', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007]}
Add value into array
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Phones, 999999}', '"189002"', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007, "189002"]}
Add field in nested object
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{Address}', '{"City": "Chengdu"}', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007, "189002"], "Address": {"City": "Chengdu"}}
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Address,Street}', '"CdUt#315"', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007, "189002"], "Address": {"City": "Chengdu", "Street": "CdUt#315"}}
Querying
The two main operators for getting results out of jsonb data are:
-> returns jsonb object field or array by key
->> returns jsonb object field as text
This article demonstrates usage of jsonb operations and storage mechanics.
select attr->>'No',attr->'Address'->>'Street' from jsbt2 where jid=15;
字段类型转换
cast(attr->>'No' as numeric)
Fulltext search
SELECT * FROM jsbt2 AS js WHERE js.attr->'Address'->>'Street' ~* '.*du.*';
UPSERT
UPSERT是INSERT, ON CONFLICT UPDATE的简写，简而言之就是：插入数据，正常时写入，主键冲突时更新。
UPDATE jsbt2 SET attr = attr - 'X' || '{"X":["Sa","Dba"]}' WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"]}
UPDATE jsbt2 SET attr = jsonb_set(attr,'{"X"}','["Sa","Dba"]'::jsonb) WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"]}
UPDATE jsbt2 SET attr = '{"X":["Sa","Dba"]}'::jsonb WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"]}
Concat
UPDATE jsbt2 SET attr = attr || '{"NoIDs":[23,15]}' WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"], "NoIDs": [23, 15]}
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{Address}', '{"City": "Shanghai"}', TRUE) where jid=13 returning *;
13 | {"X": [10, 13], "Address": {"City": "Shanghai"}}
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{No}', '36',TRUE) where jid=13 returning *;
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{Phones}','[156003,138005]',TRUE) where jid=13 returning *;
13 | {"X": [10, 13], "No": 36, "Phones": [156003, 138005], "Address": {"City": "Shanghai"}}
Delete field
UPDATE jsbt2 SET attr=attr - 'No' where jid=13 returning *;
UPDATE jsbt2 SET attr=attr #- '{Address,City}' where jid=13 returning *;
13 | {"X": [10, 13], "Phones": [156003, 138005], "Address": {}}
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Phones,2}', '137000', TRUE) where jid=13 returning *;
13 | {"X": [10, 13], "Phones": [135004, 139008, 137000], "Address": {}}
UPDATE jsbt2 SET attr=attr #- '{Phones,1}' where jid=13 returning *;
13 | {"X": [10, 13], "Phones": [135004, 137000], "Address": {}}
Renaming field
UPDATE jsbt2 SET attr=attr - 'X' || jsonb_build_object('NoIDs', attr->'X') where jid=13 returning *;
13 | {"NoIDs": [10, 13], "Phones": [135004, 137000], "Address": {}}
--------------------------------------------------------------
那么在面对这三种格式该如何选择呢？
如果你的需求是键值对存取(无对象嵌套)，并有大量操作，且需要建立索引，那么就选择HStore。
如果你的需求只是文本存取，并无大量操作，那么就选择JSON，JSON存储速度快。
如果你的需求是文本存取，且有大量操作，并需要建立索引，那么就选择JSONB。
如果是以JSON格式从外部接受数据，想以分级格式存储它，它就会是一个性能问题因为它会花费一些时间来转换它为hstore格式。
如果是从内部产生数据，而不是从它几乎完成的格式接受，并且分层不是很深，使用hstore，就为它更快的性能。
如果保留值的类型是必需的，JSON就是不二选择，因为hstore存储所有的都是字符串格式。
如果在键值对中全文档查询是必需的，当前hstore索引情况比JSON的更优秀。
如果你仅仅查询前几条(1-3)记录，那么在关键字上使用b-tree索引比使用gist或者git索引更好; 在这个方面hstore没有比json更特别优点。
json与jsonb 允许你保存一个有效的json值(定义)。例如，以下都是有效的json表示方式: null, true, [1, false, "string", {"foo":"bar"}], {"foo":"bar", "baz":[null]}.
相比json，hstore只是它的一个很小的子集(但如果需求就是这个子集的话也是相当不错的)。
Hstore是按键值对的形式直接存储在数据库中。它的优点是不需要提前定义键，直接插入要保存的内容即可，且可以在键上加索引，缺点是不支持嵌套对象存储。可以看出hstore足够的灵活性，特别是再加上postgres独有的GIN，GiST索引。
Hstore arrived way back in Postgres 8.3, before upsert, before streaming replication, and before window functions. Hstore is essentially a key/value store directly in Postgres. With hstore you're a little more limited in terms of the datatypes you have: you essentially just get strings. You also don't get any nesting; in short it's a flat key/value datatype.
但是它有两大局限：
1.它只处理文本。
2.它不支持全文档存储意味着你不能嵌套对象。
但是hstore主要特性包括可以在其上使用索引，众多操作符强有力支持，显而易见的，对数据的扩展性不够。一些基本的操作符包括：
返回列foo中关键字bar的值:
foo->"bar"
指定的列foo是否包含了关键字bar:
foo?"bar"
指定的列foo是否包含了键值对bar->baz:
foo@>"bar->baz"
JSON是直接的文本存储，所以可以嵌套对象，但是不可以使用索引。JSON在Postgres 9.3中得到更好的支持，有一些自带的操作符。
Postgres 9.2 arrived it was well received as the JSON release.Finally in Postgres 9.4 we got real and proper JSON in the form of JSONB. The B stands for better. JSONB is a binary representation of JSON, this means it's compressed and more efficient for storage than just text. It also has a similar plumbing of hstore underneath. In fact, once upon a time there was almost hstore2 and a separate JSON type, but the two converged into the JSONB we have today.
JSON中可以存储有效的JSON值，例如null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]}这些都可以存入json中。官方文档上对此有说明：
有两个JSON数据类型：json和jsonb。它们接受几乎相同的值组作为输入。它们实际的主要差别是效率。json 数据类型存储输入文本的精确拷贝，处理函数必须在每个执行上重新解析；而jsonb数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析。jsonb也支持索引，这也是一个明显的优势。
因为json类型存储输入文本的精确拷贝，它将保存令牌间语义上无关紧要的空格，和JSON对象中键的顺序。另外，如果值中的一个JSON对象多次包含相同的键，那么保存所有的键/值对。(处理函数将最后一个值当做操作值)相比之下，jsonb不保存空格，也不保存对象键的顺序，并且不保存重复对象键。如果在输入中指定了重复的键，那么只保存最后一个值。下面对比一下它们：
json与jsonb的区别主要是它们的存储方式：
json是保存为文本格式的
jsonb是保存为二进制格式的
这主要有三方面的影响：
jsonb通常比json占用更多的磁盘空间(有些情况不是)
jsonb比json的写入更耗时间
json的操作比jsonb的操作明显更耗时间(在操作一个json类型值时需要每次都去解析)
当jsonb将在未来稳定版发行可用时，这有两个主要使用情况，你很容易在他们之间选择的：
如果你的应用只用json表示，PostgreSQL只用于保存与获取时，你应该使用json。
如果你需要在PostgreSQL中做比较多的json值的操作，或者在一些json字段上使用索引时，你应该使用jsonb。
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
JSONB
站在用户的角度来说JSONB和JSON的操作方式几乎相同，都接受相同值作为输入。它们二者之间主要的区别是效率。
首先，JSON 数据类型存储输入文本的精确拷贝，处理函数必须在每个执行上重新解析，而JSONB数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析(JSONB格式要比JSON格式多占用空间)。其次，JSONB支持索引，这亦是一个明显的优势。jsonb缺省的GIN操作符类支持使用@>、?、?&和?|操作符查询。
--------------------------------------------------------------
Hstore 数据类型使用介绍
简单的说 hstore 数据类型用来存储具有多个属性值的数据，如 key => value ， key 代表存储的键， values 为相应属性对应的值。
安装该模块
# create extension hstore;
For Rpm:
yum install postgresql-contrib
https://www.postgresql.org/docs/current/static/hstore.html
http://www.postgres.cn/docs/10/hstore.html
相关的操作及函数
查看包含了相应键的记录
select * from sys_partion where partions ? '/exdsk';
查看包含了相应键值记录对的记录
select * from sys_partion where partions @> '"/exdsk"=>"12"' :: hstore;
查看包含了所有键的记录
select * from sys_partion where partions ?& ARRAY[ '/home', '/exdsk' ];
查看包含了任一键的记录
select * from sys_partion where partions ?| ARRAY[ '/home', '/exdsk' ];
转json(b)是非常容易的
select hstore_to_json(partions) from sys_partion limit 9;
将所有的键值对转集合后输出
select *,(each(partions)).* from sys_partion limit 9;
-------------------------------
CREATE TABLE books (
id serial primary key,
title VARCHAR (255),
attr hstore
);
INSERT INTO books (title, attr)
VALUES(
'PostgreSQL Tutorial',
'"paperback" => "243",
"publisher" => "www.postgresql.org",
"language" => "English",
"ISBN-13" => "978-1449370000",
"weight" => "11.2 ounces"'
);
INSERT INTO books (title, attr)
VALUES (
'PostgreSQL Cheat Sheet',
'"paperback" => "9",
"publisher" => "www.freeoa.net",
"language" => "Chinese",
"ISBN-13" => "978-1449370001",
"weight" => "1 ounces"'
);
可以根据不同的键或值进行查询
SELECT attr -> 'ISBN-13' AS isbn FROM books;
SELECT attr -> 'weight' AS weight FROM books WHERE attr -> 'ISBN-13' = '978-1449370000';
select attr->'author' as author from books where attr->'language' = 'Chinese';
为hstore字段增加k/v，如果存在则更新：
update htest set h = h || ('foo'=>'bar') where t='key';
UPDATE books SET attr = attr || '"freeshipping"=>"yes"' :: hstore;
update htest set h = coalesce(h, hstore(array[]::varchar[])) || ('foo'=>'bar') where t='key';
For Postgres version > 9.1:
UPDATE htest SET h = COALESCE(h, hstore('')) || hstore('foo', 'bar') WHERE t='key';
UPDATE books SET attributes = hstore(ARRAY[['pages', '570'],['category','gis'],['includes','code samples'],['media', 'ebook']]) WHERE id = 10;
从中删除指定键
UPDATE sys_partion SET partions = delete(partions, '/exdsk') where ipnum=181278535 and creatime='2019-08-22 11:16:02';
UPDATE books SET attr = delete(attr, 'freeshipping');
从hstore类型列中取得所有的键
To get all keys from an hstore column, you use the akeys() function as follows:
SELECT akeys(attr) FROM books;
Or you can use the skey() function if you want PostgreSQL to return the result as a set.
SELECT skeys(attr) FROM books;
Get all values from an hstore column
Like keys, you can get all values from an hstore column using the avals() function in the form of arrays.
SELECT avals (attr) FROM books;
Or you can use the svals() function if you want to get the result as a set.
SELECT svals(attr) FROM books;
Convert hstore data to JSON
PostgreSQL provides the hstore_to_json() function to convert hstore data to JSON:
SELECT title,hstore_to_json(attr) json FROM books;
Convert hstore data to sets
To convert hstore data to sets, you use the each() function as follows:
SELECT title, (EACH(attr)).* FROM books;
--------------------------------------------------------------
JSON 数据类型使用介绍
JSON stands for JavaScript Object Notation. JSON is an open standard format that consists of key-value pairs. The main usage of JSON is to transport data between a server and web application. Unlike other formats, JSON is human-readable text.
JSON 数据类型是用来存储 JSON(JavaScript Object Notation) 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。
PostgreSQL supports native JSON data type since version 9.2. It provides many functions and operators for manipulating JSON data.
JSON Types
http://www.postgres.cn/docs/10/datatype-json.html
https://www.postgresql.org/docs/current/static/datatype-json.html
JSON Functions and Operators
http://www.postgres.cn/docs/10/functions-json.html
https://www.postgresql.org/docs/current/static/functions-json.html
Postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率：json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等；而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同，使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。
注意：键值对的键必须使用双引号。
数据的类型转换
to cast the json value to text and then to float.
(json_data #>> '{field}')::float
(json_data->'position'->'lat')::text::float cast
查hdfs每天每类型的空间占用量
SELECT AVG (( hj.hdnn_fsn ->> 'CapacityUsedGB' ) :: INT
) :: INT AS used_gb,
hin.hbname,
hj.cdt :: DATE AS dt
FROM
hdbjmx hj,hbidname hin
WHERE
hj.hbid=hin.hbid and
hj.htype = 'nn'
AND hj.cdt BETWEEN '2017-11-20 00:00:00' AND '2017-11-26 23:59:59'
GROUP BY
hin.hbname,dt
CREATE TABLE orders (
ID serial NOT NULL PRIMARY KEY,
info json NOT NULL
);
The orders table consists of two columns:
The id column is the primary key column that identifies the order.
The info column stores the data in the form of JSON.
INSERT INTO orders (info)
VALUES('{ "customer": "John 郑", "items": {"product": "Beer","qty": 6}}');
INSERT INTO orders (info) VALUES
('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 11}}'),
('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');
PG提供了两种原生的访问操作符来访问JSON数据:->,->>
PostgreSQL provides two native operators -> and ->> to help you query JSON data.There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text.
The operator -> returns JSON object field by key.
The operator ->> returns JSON object field by text.
数据类型转换
CAST (info -> 'items' ->> 'qty' AS INTEGER)
PostgreSQL JSON 函数
json(b) 的常用函数及操作符
下面是对JSON格式操作的一些例子：
--获取JSON数组元素(索引从0开始)
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;
--获取JSON对象
select '{"a": {"b":"foo"}}'::json->'a';
SELECT '[1,2,3]'::json->>2;
--获取JSON对象的文本(as text)
select '{"a":1,"b":2}'::json->>'b';
--根据路径获取JSON对象
select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';
--根据路径获取JSON对象的文本
select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
--指定的路径获取JSON对象#>，右操作符为: text[]
SELECT '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';
即在获取a.b的值
--指定的路径获取JSON对象为text，#>>右操作符为: text[]
SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
即获取a[2]的值并转为text
再补充一个实际中的例子：
CREATE TABLE json_test (
id serial PRIMARY KEY,
attributes json
);
INSERT INTO json_test (attributes)
VALUES ( '{"hello": "hello-value",
"world": {"world_type": "earth","world_value": "world"}
}');
--这里的俩条查询语句我之所以用 #>>这个操作符，因为等号右边是文本
select * from json_test
where attributes #>> '{hello}' = 'hello-value';
select * from json_test
where attributes #>> '{world,world_type}' = 'earth';
除了上述的->，->>， #>， #?这些操作符外，还有很多操作符以及函数，可以说是相当丰富了。
jsonb 操作符
@>
右操作数的类型： jsonb, 左侧的JSONB的是否包含右侧的
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;
<@
右操作数的类型： jsonb, 右侧的JSONB的是否包含左侧的
SELECT '{"a":1, "b":2}'::jsonb <@ '{"b":2}'::jsonb;
SELECT '{"a":1, "b":2}'::jsonb <@ '{"b":2,"c":3,"a":1}'::jsonb;
?
右操作符: text, 该字符串是否存在于json的顶级key中
SELECT '{"a":1, "b":2}'::jsonb ? 'b';
?|
右操作符：text[]，这些元素之一是否存在于json的顶级key中
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'cee', 'e'];
?&
右操作符：text[]，所有这些元素是否存都在于json的顶级key中
SELECT '["a", "b"]'::jsonb ?& array['a', 'b'];
SELECT '["a", "b"]'::jsonb ?& array['a', 'b', 'c'];
||
右操作符: jsonb, 拼接两个jsonb生成一个新的jsonb
SELECT '["a", "b", {"hello":"world"}]'::jsonb || '["c", "d", {"hello":"world"}]'::jsonb;
-
右操作符：text，从左操作数中删除K/V或者字符串元素
SELECT '{"a": "b","c":[1,5,9]}'::jsonb - 'c';
右操作符：int， 删除指定索引的元素(负数表示从结尾开始)
SELECT '["a", "b"]'::jsonb - (-1);
SELECT '["a", "b"]'::jsonb - 1;
#-
右操作符： text[]， 删除字段或指定路径的元素
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';
-----------
["a", {}]
SELECT '["a", {"b":1},{"c":2,"f":5}]'::jsonb #- '{2,c}';
---------------------------
["a", {"b": 1}, {"f": 5}]
SELECT '["a", {"b":1}]'::jsonb #- '{0}';
------------
[{"b": 1}]
常用json函数
json_each() function allows us to expand the outermost JSON object into a set of key-value pairs,get a set of key-value pairs as text, you use the json_each_text().
json_object_keys() get a set of keys in the outermost JSON object.
json_typeof() function returns type of the outermost JSON value as a string. It can be number, boolean, null, object, array, and string.
row_to_json,array_to_json
select row_to_json(table_name) from table_name;
select array_to_json(array_agg(province)) from province;
select array_to_json(array_agg(row_to_json(province))) from province;
json(b)中的CRUD
# \d+ sp2
数据表 "monsys.sp2"
栏位 | 类型 | 修饰词 | 存储 | 统计目标 | 描述
----------+-----------------------------+--------------------------------------------------------------+----------+----------+------
ipnum | bigint | 非空 默认 '2130706433'::bigint | plain | |
creatime | timestamp without time zone | 非空 默认 '2019-01-01 00:00:00'::timestamp without time zone | plain | |
partions | json
添加jsonb的字段
select * from sp2 where ipnum=181278536 and creatime='2019-08-13 18:19:26';
--为partions添加一个'/home'分区，并为其设定一个值。
UPDATE sp2 set partions = jsonb_set(partions, '{/home}', '"95"', true) where ipnum=181278536 and creatime='2019-08-13 18:19:26';
--修改已经存的键的值
UPDATE sp2 set partions = jsonb_set(partions, '{/boot}', '"99"'::text::jsonb, true) where ipnum=181278536 and creatime='2019-08-13 18:19:26';
UPDATE sp2 set partions = jsonb_set(partions, '{/boot}', '"90"'::jsonb, true) where ipnum=181278536 and creatime='2019-08-13 18:19:26';
增加或修改键(key)
UPDATE dbs set attr = attr || '{"islave":"1","Seconds_Behind_Master":"1000"}' where ipaddr='...' returning *;
删除jsonb的某字段
--将'/boot'键从中移除
UPDATE sp2 set partions = (partions - '/boot') where ipnum=181278536 and creatime='2019-08-13 18:19:26';
移除其中的键(key)
UPDATE dbs SET attr = attr::jsonb - '{islave,Seconds_Behind_Master}'::text[] where ipaddr='...' returning *;
Jsonb操作手记
当为表中属性字段(attr)记录设置一个认证密码的key属性时
UPDATE dbs set attr = attr || '{"authpwd":"zjLFreeOAx"}' where id in (109);
UPDATE dbs set attr = jsonb_set(attr, '{authpwd}', '"zw123x"') where id in (111,156);
发现上面自redis操作没有成功，是因为attr字段为全空，而非json中的空('{}')，因此上面的update会成功但结果为空(没有)。
因此将属性字段重置为json中的空'{}'(注意：是空字段NULL)
UPDATE dbs set attr = '{}' where attr is null;
UPDATE dbs set attr = jsonb_set(attr, '{authpwd}', '"vgFreeOAei"', true) where assetsid=103 and dbcat='mysql';
在PostgreSQL中的json类型字段检查是否存在相关的key字段
use ->:
where (json->'attribute') is not null
查找103区域的从库信息
select id,ipaddr,dbcat,attr from dbs where assetsid=103 and (attr->'islave') is not null;
可以使用json类型专用的'?'操作符
WHERE column_name::jsonb ? 'attribute'
NOTE: Only for jsonb type.
select id,ipaddr,dbcat,attr from dbs where assetsid=103 and attr?'islave';
PgSQL的不同版本对json类型的数据操作上略有不同，请在操作时注意数据库服务器的版本。下面再举一示例来体现：
create table jsbt2(jid int2 not null,attr jsonb);
insert into jsbt2 values(11,'{"A":"B"}');
insert into jsbt2 values(12,'{"X":"FreeOA"}');
将json对象中的"A"的值更改为当前时间的预操作
SELECT jsonb_set('{"A":"B"}', '{"A"}', to_jsonb(now()));
对attr这个json对象中的字段进行值的更新
UPDATE jsbt2 SET attr = jsonb_set('{"A":"B"}', '{"A"}', to_jsonb(now())) where jid=11;
对attr这个json对象中的字段新增key并进行值的更新
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', to_jsonb(now())) where jid=11 returning *;
对attr这个json字段中的两key的值对比
SELECT (attr #>>'{A}') < (attr #>>'{X}') FROM jsbt2 where jid=11;
执行失败
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X, Lone}', '"Love"') where jid=12 returning *;
错误: 22P02: 位置 2 的路径元素不是一个整数："Lone"
为其中的json key加入数组元素
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X,1}', '"Love"') where jid=12 returning *;
12 | {"X": ["Love"]}
为符合条件的记录加上X这个key，有的话将其置为空数组([])
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', '[]') where;
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', '10') where jid=13;
UPDATE jsbt2 SET attr = jsonb_set(attr, '{X}', '[10,13]') where jid=13 returning *;
刪掉json中key下面的某键数组中的值：
UPDATE jsbt2 SET attr = attr #- '{X, 1}' where jid=13 returning *;
13 | {"X": [10]}
会将数组中的'13'这个数组值移除掉。
添加键与值
To add fields and values in jsonb, deep object structures, or arrays, we must refer back to JSON Processing Functions article. For many of the operations we use the jsonb_set function which is defined like:
jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])
It returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist.
Full parameter list of jsonb_set():
jsonb_set(target jsonb,
path text[],
new_value jsonb,
create_missing boolean default true)
path can contain JSON array indexes too & negative integers that appear there count from the end of JSON arrays.
insert into jsbt2 values(15,'{"Age":37,"No":7}');
Add field
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Age}', '"36"', TRUE) where jid=12 returning *;
12 | {"No": 7, "Age": "36"}
Add array
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Phones}', '[138003,139007]', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007]}
Add value into array
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Phones, 999999}', '"189002"', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007, "189002"]}
Add field in nested object
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{Address}', '{"City": "Chengdu"}', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007, "189002"], "Address": {"City": "Chengdu"}}
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Address,Street}', '"CdUt#315"', TRUE) where jid=15 returning *;
15 | {"No": 7, "Age": "30", "Phones": [138003, 139007, "189002"], "Address": {"City": "Chengdu", "Street": "CdUt#315"}}
Querying
The two main operators for getting results out of jsonb data are:
-> returns jsonb object field or array by key
->> returns jsonb object field as text
This article demonstrates usage of jsonb operations and storage mechanics.
select attr->>'No',attr->'Address'->>'Street' from jsbt2 where jid=15;
字段类型转换
cast(attr->>'No' as numeric)
Fulltext search
SELECT * FROM jsbt2 AS js WHERE js.attr->'Address'->>'Street' ~* '.*du.*';
UPSERT
UPSERT是INSERT, ON CONFLICT UPDATE的简写，简而言之就是：插入数据，正常时写入，主键冲突时更新。
UPDATE jsbt2 SET attr = attr - 'X' || '{"X":["Sa","Dba"]}' WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"]}
UPDATE jsbt2 SET attr = jsonb_set(attr,'{"X"}','["Sa","Dba"]'::jsonb) WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"]}
UPDATE jsbt2 SET attr = '{"X":["Sa","Dba"]}'::jsonb WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"]}
Concat
UPDATE jsbt2 SET attr = attr || '{"NoIDs":[23,15]}' WHERE jid=14 returning *;
14 | {"X": ["Sa", "Dba"], "NoIDs": [23, 15]}
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{Address}', '{"City": "Shanghai"}', TRUE) where jid=13 returning *;
13 | {"X": [10, 13], "Address": {"City": "Shanghai"}}
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{No}', '36',TRUE) where jid=13 returning *;
UPDATE jsbt2 SET attr = jsonb_insert(attr, '{Phones}','[156003,138005]',TRUE) where jid=13 returning *;
13 | {"X": [10, 13], "No": 36, "Phones": [156003, 138005], "Address": {"City": "Shanghai"}}
Delete field
UPDATE jsbt2 SET attr=attr - 'No' where jid=13 returning *;
UPDATE jsbt2 SET attr=attr #- '{Address,City}' where jid=13 returning *;
13 | {"X": [10, 13], "Phones": [156003, 138005], "Address": {}}
UPDATE jsbt2 SET attr = jsonb_set(attr, '{Phones,2}', '137000', TRUE) where jid=13 returning *;
13 | {"X": [10, 13], "Phones": [135004, 139008, 137000], "Address": {}}
UPDATE jsbt2 SET attr=attr #- '{Phones,1}' where jid=13 returning *;
13 | {"X": [10, 13], "Phones": [135004, 137000], "Address": {}}
Renaming field
UPDATE jsbt2 SET attr=attr - 'X' || jsonb_build_object('NoIDs', attr->'X') where jid=13 returning *;
13 | {"NoIDs": [10, 13], "Phones": [135004, 137000], "Address": {}}
--------------------------------------------------------------
那么在面对这三种格式该如何选择呢？
如果你的需求是键值对存取(无对象嵌套)，并有大量操作，且需要建立索引，那么就选择HStore。
如果你的需求只是文本存取，并无大量操作，那么就选择JSON，JSON存储速度快。
如果你的需求是文本存取，且有大量操作，并需要建立索引，那么就选择JSONB。
如果是以JSON格式从外部接受数据，想以分级格式存储它，它就会是一个性能问题因为它会花费一些时间来转换它为hstore格式。
如果是从内部产生数据，而不是从它几乎完成的格式接受，并且分层不是很深，使用hstore，就为它更快的性能。
如果保留值的类型是必需的，JSON就是不二选择，因为hstore存储所有的都是字符串格式。
如果在键值对中全文档查询是必需的，当前hstore索引情况比JSON的更优秀。
如果你仅仅查询前几条(1-3)记录，那么在关键字上使用b-tree索引比使用gist或者git索引更好; 在这个方面hstore没有比json更特别优点。