以下举例说明：
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)