Postgres 9.2 亮点: JSON 数据类型——Michael Paquier
PostgreSQL 9.2 引入了一个与JSON相关的新特性；内置的数据类型。你现在可以把JSON字段直接存储于你的数据库内部，而无需外部格式检查器。因为现在它直接就在Postgres的内核中。该特性已通过提下列内容添加。
commit 5384a73f98d9829725186a7b65bf4f8adb3cfaf1
Author: Robert Haas
Date: Tue Jan 31 11:48:23 2012 -0500
Built-in JSON data type.
Like the XML data type, we simply store JSON data as text, after checking
that it is valid. More complex operations such as canonicalization and
comparison may come later, but this is enough for now.
There are a few open issues here, such as whether we should attempt to
detect UTF-8 surrogate pairs represented as \uXXXX\uYYYY, but this gets
the basic framework in place.@postgresql.org>
一些系统功能随后也被添加，以输出一些json形式的行或列数据。
commit 39909d1d39ae57c3a655fc7010e394e26b90fec9
Author: Andrew Dunstan
Date: Fri Feb 3 12:11:16 2012 -0500
Add array_to_json and row_to_json functions.
Also move the escape_json function from explain.c to json.c where it
seems to belong.
Andrew Dunstan, Reviewed by Abhijit Menon-Sen.@dunslane.net>
事实上Postgres 内核对JSON字段的处理是将其存储为文本字段 (对大为1GB) ，更重要的是字符串格式校验可以被直接执行于核中。让我们在一个实际使用案例中运用一下它，一个表存储着一个RPG游戏里的商品列表，包含多种类型的商品，每一个统计都包含不同的字段。比如说一把剑有攻击值，一个盾牌有防御值，如果相反就不符合逻辑（除非剑带有魔法防御加成效果，盾牌有火焰防护…）好吧，我的意思是你不需要为每件物品建立多张表，而可以将数据存进一张特殊的物品表，这要归功于JSON的扩展性。有了Postgres核内完成格式检查，你也无需在应用端执行字符串格式校验。
postgres=# CREATE TABLE rpg_items (c1 serial, data json);
CREATE TABLE
postgres=# INSERT INTO rpg_items (data) VALUES
postgres-# ('{"name":"sword","buy":"500","sell":"200","description":"basic sword","attack":"10"}');
INSERT 0 1
postgres=# INSERT INTO rpg_items (data) VALUES
postgres-# ('{"name":"shield","buy":"200","sell":"80","description":"basic shield","defense":"7"}');
INSERT 0 1
postgres=# SELECT * FROM rpg_items;
c1 | data
----+--------------------------------------------------------------------------------------
1 | {"name":"sword","buy":"500","sell":"200","description":"basic sword","attack":"10"}
2 | {"name":"shield","buy":"200","sell":"80","description":"basic shield","defense":"7"}
(2 rows) (2 rows)
为防格式错误，你将获得一些类似一下的东西：
postgres=# INSERT INTO rpg_items (data) VALUES ('{"name":"dummy","buy":"200","ppo"}');
ERROR: invalid input syntax for type json
LINE 1: INSERT INTO rpg_items (data) VALUES ('{"name":"dummy","buy":...LINE 1: INSERT INTO rpg_items (data) VALUES ('{"name":"dummy","buy":...
那么，你也可以操纵现存的表并以JSON的形式将数据输出至客户端。
postgres=# CREATE TABLE rpg_items_defense (c1 serial, buy int, sell int, description text, defense int);
CREATE TABLE
postgres=# INSERT INTO rpg_items_defense (buy, sell,description, defense)
postgres-# VALUES (200, 80, 'basic shield', 7);
INSERT 0 1
postgres=# SELECT row_to_json(row(buy,sell,description,defense)) FROM rpg_items_defense;
row_to_json
-----------------------------------------------
{"f1":200,"f2":80,"f3":"basic shield","f4":7}
(1 row)
字段名有Postgres自动生成的默认值。
或是输JSON队列值。
postgres=# CREATE TABLE rpg_items_attack(int serial, fields int[], description text);
CREATE TABLE
postgres=# INSERT INTO rpg_items_attack (fields, description) VALUES
postgres-# ('{500,200,10}','basic sword');
INSERT 0 1
postgres=# SELECT row_to_json(row(array_to_json(fields), description)) FROM rpg_items_attack;
row_to_json
----------------------------------------
{"f1":[500,200,10],"f2":"basic sword"}
(1 row)
当然这不是唯一可行的解决方案。在设计你的应用时要慎重选择。
