[翻译]PostgreSQL的JSON类型可以干啥？

这是一篇翻译，原文地址：http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/

PostgreSQL9.2版本开始引入原生JSON类型的支持，不过官方没有提供更多的支持。
你可以使用以下3种方案使JSON类型的真正能够使用起来：

1. 等待PostgreSQL9.3版本。
2. 使用[plv8]扩展,
   通过这个扩展能提供更多的扩展性（你可以自定义自己的函数）
3. 使用[json_enhancements] 扩展，这个扩展是将9.3的JSON函数移植到9.2。

现在我准备开始使用这个新玩意，而我使用的是方案3。我写了另外一篇POST去说明怎样去操作。 adding json_enhancements to PostgreSQL 9.2.

假设你已经在使用9.3或者安装了json_enhancements扩展的9.2版本。
那你现在能干啥呢？答案是：非常多！JSON类型的操作及函数你可以在这里[9.3 documentation]找到。
我准备展示一些有趣的真实场景的例子。

开始：

创建一个数据，执行以语句：

createdb json_test
psql json_test

填充一些数据：

CREATE TABLE books ( id integer, data json );

INSERT INTO books VALUES (1,
  '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }');
INSERT INTO books VALUES (2,
  '{ "name": "Book the Second", "author": { "first_name": "Charles", "last_name": "Xavier" } }');
INSERT INTO books VALUES (3,
  '{ "name": "Book the Third", "author": { "first_name": "Jim", "last_name": "Brown" } }');

查询

你可以在JSON数据列中提取JSON数据的内容：

SELECT id, data->>'name' AS name FROM books;

 id |      name
----+-----------------
  1 | Book the First
  2 | Book the Second
  3 | Book the Third

“->” 操作符返回原生JSON类型对象，”->>” 操作返回字符串对象。你可以通过”->” 操作返回子对象，然后组成链式操作：

SELECT id, data->'author'->>'first_name' as author_first_name FROM books;

 id | author_first_name
----+-------------------
  1 | Bob
  2 | Charles
  3 | Jim

是不是Cool毙了？

条件查询

当然，你可以使用JSON数据作为查询条件：

SELECT * FROM books WHERE data->>'name' = 'Book the First';

 id |                                         data
----+---------------------------------------------------------------------------------------
  1 | '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }'

你也可以使用更深层级的JSON类型查询

SELECT * FROM books WHERE data->'author'->>'first_name' = 'Charles';

 id |                                            data
----+---------------------------------------------------------------------------------------------
  2 | '{ "name": "Book the Second", "author": { "first_name": "Charles", "last_name": "Xavier" } }'

索引

你可以通过使用PostgreSQL的[表达式索引]，索引JSON类型的子项，这意味着你可以为的JSON数据子项添加唯一索引限制：

CREATE UNIQUE INDEX books_author_first_name ON books ((data->'author'->>'first_name'));

INSERT INTO books VALUES (4,
  '{ "name": "Book the Fourth", "author": { "first_name": "Charles", "last_name": "Davis" } }');
ERROR:  duplicate key value violates unique constraint "books_author_first_name"
DETAIL:  Key (((data -> 'author'::text) ->> 'first_name'::text))=(Charles) already exists.

表达式索引的创建，是比较消耗资源的，不过当你创建了之后，查询JSON数据会快很多！

一个真实场景中的例子

OK，现在给出一个真实场景中的例子，在一个监控系统（tracking system）中，有一个event表。

CREATE TABLE events (
  name varchar(200),
  visitor_id varchar(200),
  properties json,
  browser json
);

我们将一些事件（Events）信息保存到这个表中，例如 pageview。每种事件都有若干属性，可以是任何值，例如：current page（当前页面url），或者是浏览者信息（例如：神码系统，神码浏览器，分辨率是多少等）。这个属性全部都是松散的可变化的，可能在之后还会扩充更多的属性……

现在我们插入一些数据：

INSERT INTO events VALUES (
  'pageview', '1',
  '{ "page": "/" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
);
INSERT INTO events VALUES (
  'pageview', '2',
  '{ "page": "/" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1920, "y": 1200 } }'
);
INSERT INTO events VALUES (
  'pageview', '1',
  '{ "page": "/account" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
);
INSERT INTO events VALUES (
  'purchase', '5',
  '{ "amount": 10 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1024, "y": 768 } }'
);
INSERT INTO events VALUES (
  'purchase', '15',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);
INSERT INTO events VALUES (
  'purchase', '15',
  '{ "amount": 500 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);

看见如此零散的数据，让我们不得不提起文档类数据库MongoDB，这货处理这类松散结构比较在行，之后让你看看 PostgreSQL 一样处理得很好！

统计数据

使用JSON操作符及SQL的统计函数，就可以得到我们想要的了！而且是在一个关系型数据库上实现的～

浏览器统计

SELECT browser->>'name' AS browser, count(browser)
FROM events
GROUP BY browser->>'name';

 browser | count
---------+-------
 Firefox |     3
 Chrome  |     2

每访问者收入

SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total
FROM events
WHERE CAST(properties->>'amount' AS integer) > 0
GROUP BY visitor_id;

 visitor_id | total
------------+-------
 5          |    10
 15         |   700
 

分辨率平均值

SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,
  AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height
FROM events;

         width         |        height
-----------------------+----------------------
 1397.3333333333333333 | 894.6666666666666667