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

巴特西

pg的json类型

最新文章

热门文章