PostgreSQL 9.4: 新增 JSONB 数据类型

         PostgreSQL9.4 新增 JSONB 数据类型， JSONB 同时属于 JSON (JavaScript Object Notation) 数据类型，jsonb 和 json 的输入数据几乎完全通用，最大的差别体现在效率上，json 存储的数据几乎和输入数据一样，存储的是未解析的数据，调用函数时使用效率较低; 而 jsonb 存储的是分解的 binary 格式数据，使用时不需要再解析了，因此使用上效率较高; 另一方面 json 在写入时较快，而 jsonb 写入时由于需要转换导致写入较慢。下面通过些简单的例子了解两者的差异。

  

--1 这个例子两者没啥差异

 

francs=> SELECT '[1, 2, "foo", null]'::json;

        json         

---------------------

 [1,2,"foo",null]

(1 row)

 

francs=> SELECT '[1, 2, "foo", null]'::jsonb;

        jsonb        

---------------------

 [1,2,"foo",null]

(1 row)

备注:  json 类型输出的内容和写入的内容一样，不会对输出的结果改变，而 jsonb不一样，看下面的例子。

 

--2 jsonb 输出内容顺序不一样

 

francs=> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;

                      json                       

-------------------------------------------------

 {"bar":"baz","balance":7.77,"active":false}

(1 row)

 

francs=> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;

                      jsonb                       

--------------------------------------------------

 {"bar":"baz","active":false,"balance":7.77}

(1 row)

 

--3 jsonb: 整数类型输出不一样

 

francs=> SELECT '{"reading": 1.230e-5}'::json,'{"reading": 1.230e-5}'::jsonb;

         json          |          jsonb          

-----------------------+-------------------------

 {"reading":1.230e-5}|{"reading":0.00001230}

(1 row)

 

--4 jsonb: 去掉了空格

 

francs=>select' {"id":1,

"name":"francs",

"remark":"a good guy!"

}'::json;

          json          

------------------------

  {"id":1,             +

 "name":"francs",      +

 "remark":"a good guy!"+

 }

(1 row)

 

francs=>select' {"id":1,

"name":"francs",

"remark":"a good guy!"

}'::jsonb;

                        jsonb                         

------------------------------------------------------

 {"id":1,"name":"francs","remark":"a good guy!"}

(1 row)

 

--5 jsonb:  重复的元素值仅保留最后一个

 

francs=>select' {"id":1,

"name":"francs",

"remark":"a good guy!",

"name":"test"

}'::jsonb;

                       jsonb                        

----------------------------------------------------

 {"id":1,"name":"test","remark":"a good guy!"}

(1 row)

备注： json 类型的输出和输入一样，会保留所有重复的元素，而 jsonb 对于重复的元素仅保留最后出现的重复元素。

 

 

--6 关于索引

 

  GIN  索引支持 jsonb 类型，支持大的 jsonb 表中基于 keys 或者 key/values 模式的检索。

   默认的 GIN 索引模式支持带有 @>, ?, ?& 和 ?| 操作的查询，关于这些操作符的含义参考本文的附录。

  

 假如有一个文档：

 

{

    "guid":"9c36adc1-7fb5-4d5b-83b4-90356a46061a",

    "name":"Angela Barton",

    "is_active":true,

    "company":"Magnafone",

    "address":"178 Howard Place, Gulf, Washington, 702",

    "registered":"2009-11-07T08:53:22 +08:00",

    "latitude":19.793713,

    "longitude":86.513373,

    "tags":[

        "enim",

        "aliquip",

        "qui"

    ]

}

 

我们将表名定义为 api,  jsonb 字段为 jdoc，创建如下索引

CREATE INDEX idx_gin_api_jdoc ON api USING gin (jdoc);

 

那么如下的查询可以使用索引

 

--Find documents in which the key "company" has value "Magnafone"

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @>'{"company": "Magnafone"}';

备注：上面这个例子来自手册。

 

7 附 Additional jsonb Operators

Operator Right Operand Type Description Example

=	jsonb	Are the two JSON values equal?	'[1,2,3]'::jsonb = '[1,2,3]'::jsonb
@>	jsonb	Does the left JSON value contain within it the right value?	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@	jsonb	Is the left JSON value contained within the right value?	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?	text	Does the key/element string exist within the JSON value?	'{"a":1, "b":2}'::jsonb ? 'b'
?|	text[]	Do any of these key/element strings exist?	'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?&	text[]	Do all of these key/element strings exist?	'["a", "b"]'::jsonb ?& array['a', 'b']