pg的json类型

以下举例说明：

postgres=# select '{"b":1,"a":2}'::json; 
     json      
---------------
 {"b":1,"a":2}
(1 row)

创建json字段表：

postgres=# drop table if exists test_json1;
NOTICE:  table "test_json1" does not exist, skipping
DROP TABLE
postgres=# create table test_json1(id serial primary key,name json);
CREATE TABLE

插入json数据：

postgres=# insert into test_json1(name) values('{"col1":1,"col2":"user1","col3":"male"}');
INSERT 0 1
postgres=# insert into test_json1(name) values('{"col1":2,"col2":"user2","col3":"female"}');
INSERT 0 1
postgres=# select * from test_json1;
 id |                   name                    
----+-------------------------------------------
  1 | {"col1":1,"col2":"user1","col3":"male"}
  2 | {"col1":2,"col2":"user2","col3":"female"}
(2 rows)

获取指定key的value值：

postgres=# select name -> 'col2' from test_json1 where id = 1;
 ?column? 
----------
 "user1"
(1 row)

postgres=# select name ->> 'col2' from test_json1 where id = 1;
 ?column? 
----------
 user1
(1 row)

json和jsonb的区别，先看例子：

postgres=# select ' {"a":3 ,"b":1, "a":2}'::json;
          json          
------------------------
  {"a":3 ,"b":1, "a":2}
(1 row)

postgres=# select ' {"a":3 ,"b":1, "a":2}'::jsonb;
      jsonb       
------------------
 {"a": 2, "b": 1}
(1 row)

1，jsonb存储的是二进制类型（存储慢，获取快），json是文本类型（存储快，获取慢）。

2，jsonb可以调整键的顺序。
3，jsonb可以删除多余的空格。
4，jsonb可以删除重复的键。
判断字符是否顶层键值。

postgres=# select '{"b":"1","a":"2"}'::jsonb ? 'a'; 
 ?column? 
----------
 t
(1 row)

删除数据的键/值

postgres=# select '{"b":"1","a":"2"}'::jsonb - 'a';
  ?column?  
------------
 {"b": "1"}
(1 row)

json函数

1，json_each扩展最外层的json对象成为一组键/值结果集，如下所示：

postgres=# select * from json_each('{"b":"1","a":"2"}'); 
 key | value 
-----+-------
 b   | "1"
 a   | "2"
(2 rows)

2，json_each_text以文本返回结果，如下所示：

postgres=# select * from json_each_text('{"b":"1","a":"2"}');
 key | value 
-----+-------
 b   | 1
 a   | 2
(2 rows)

3，row_to_json普通表转为json格式：

postgres=# drop table if exists test_copy;
NOTICE:  table "test_copy" does not exist, skipping
DROP TABLE
postgres=# create table test_copy as select 1 as id,'a' as name;
WARNING:  column "name" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
SELECT 1
postgres=# select * from test_copy where id = 1;
 id | name 
----+------
  1 | a
(1 row)

postgres=# select row_to_json(test_copy) from test_copy where id = 1; 
     row_to_json     
---------------------
 {"id":1,"name":"a"}
(1 row)

4，json_object_keys返回最外层的json对象中的键的集合

postgres=# select * from json_object_keys('{"b":"1","a":"2"}');
 json_object_keys 
------------------
 b
 a
(2 rows)

json键/值的追加“||”

postgres=# select '{"b":"1","a":"2"}'::jsonb||'{"d":"3","c":"4"}'::jsonb;
                 ?column?                 
------------------------------------------
 {"a": "2", "b": "1", "c": "4", "d": "3"}
(1 row)

json键/值的删除“-”

postgres=# select '{"b":"1","a":"2"}'::jsonb - 'a'; 
  ?column?  
------------
 {"b": "1"}
(1 row)

删除嵌套json数据“#-”

postgres=# select '["a","b","c"]'::jsonb - 0; 
  ?column?  
------------
 ["b", "c"]
(1 row)

postgres=# select '{"name":"bob","contact":{"phone1":"1234","phone2":"5678"}}'::jsonb #- '{contact,phone1}'::text[];
                    ?column?                    
------------------------------------------------
 {"name": "bob", "contact": {"phone2": "5678"}}
(1 row)

postgres=# select '{"name":"bob","contact":["phone1","phone2","phone3"]}'::jsonb #- '{contact,0}'::text[]; 
                     ?column?                     
--------------------------------------------------
 {"name": "bob", "contact": ["phone2", "phone3"]}
(1 row)

json值的更新，jsonb_set函数，
格式：jsonb_set(target jsonb,path text[],new_value jsonb[, create_missing boolean])
target指源jsonb数据，path指路径，new_value指更新后的键值，create_missing值为true表示键不存在则添加，为false表示如果键不存在则不添加

postgres=# select jsonb_set('{"name":"bob","age":"27"}'::jsonb,'{age}','"28"'::jsonb,false); 
          jsonb_set           
------------------------------
 {"age": "28", "name": "bob"}
(1 row)

postgres=# select jsonb_set('{"name":"bob","age":"27"}'::jsonb,'{age}','"28"'::jsonb,true);
          jsonb_set           
------------------------------
 {"age": "28", "name": "bob"}
(1 row)

postgres=# select jsonb_set('{"name":"bob","age":"27"}'::jsonb,'{sex}','"male"'::jsonb,false);
          jsonb_set           
------------------------------
 {"age": "27", "name": "bob"}
(1 row)

postgres=# select jsonb_set('{"name":"bob","age":"27"}'::jsonb,'{sex}','"male"'::jsonb,true);
                  jsonb_set                  
---------------------------------------------
 {"age": "27", "sex": "male", "name": "bob"}
(1 row)

参考：
https://www.postgresql.org/docs/9.4/functions-json.html