PostgreSQL 9.2 版本引入Json作为基本类型，它可以存储json对象，使得PostgreSQL 支持无Schema的NoSQL特性。PostgreSQL 9.4 有加入了jsonb 类型，本文带你了解jsonb类型，并通过示例学习相关操作。

JSONB类型

json类型以文本方式存储json对象，所以每次处理时需要解析和分析文本格式的json。另外还存储了不必要的空白字符和重复键。jsonb类型转换文本格式json对象未二进制格式，并删除了不需要的白色空格及重复键。因为有了预处理，jsonb需要更多空间和处理能力，但对jsonb的处理会更有效。当然所有json的操作都支持，而且还支持gin类型索引。

示例

创建数据库并填充示例数据：

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

INSERT INTO books(client, data) values ( 'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),( 'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),( 'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

SELECT * FROM books;

返回结果：

id	client	data
1	Joe	{“title”: “Siddhartha”, “author”: {“last_name”: “Hesse”, “first_name”: “Herman”}}
2	Jenny	{“title”: “Dharma Bums”, “author”: {“last_name”: “Kerouac”, “first_name”: “Jack”}}
3	Jenny	{“title”: “100 años de soledad”, “author”: {“last_name”: “Marquéz”, “first_name”: “Gabo”}}

-> ->> 操作

首先看 -> :

SELECT client, 
   data->'title' AS title, data->'author' AS author
   FROM books;	

client	title	author
Joe	“Siddhartha”	{“last_name”: “Hesse”, “first_name”: “Herman”}
Jenny	“Dharma Bums”	{“last_name”: “Kerouac”, “first_name”: “Jack”}
Jenny	“100 años de soledad”	{“last_name”: “Marquéz”, “first_name”: “Gabo”}

再通过 ->>操作对比：

SELECT client, 
   data->>'title' AS title, data->'author'->>'first_name' AS author
   FROM books;

client	title	author
Joe	Siddhartha	Herman
Jenny	Dharma Bums	Jack
Jenny	100 años de soledad	Gabo

->返回json对象， ->> 返回文本值。

过滤操作

 SELECT  client, data->>'title' AS title
 FROM books
 WHERE data->>'title' = 'Dharma Bums';

返回结果：

client	title
Jenny	Dharma Bums

嵌套过滤

SELECT  client, data->>'title' AS title
FROM books
WHERE data->'author'->>'last_name' = 'Kerouac';

返回结果：

client	title
Jenny	Dharma Bums

实战案例

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

假如我们存入页面浏览的事件，每个事件有属性，如当前页面、也包括客户端信息(如，操作系统，屏幕分辨率等)。因为没有schema限制，可以存储任意内容。

载入示例数据：

INSERT INTO events (name, visitor_id, properties, browser) VALUES
(
  'pageview', '1',
  '{ "page": "/" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'pageview', '2',
  '{ "page": "/" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1920, "y": 1200 } }'
),(
  'pageview', '1',
  '{ "page": "/account" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'purchase', '5',
  '{ "amount": 10 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1024, "y": 768 } }'
),(
  'purchase', '15',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),(
  'purchase', '15',
  '{ "amount": 500 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);
-- 验证结果
select * from events;

结果如下：

name	visitor_id	properties	browser
pageview	1	{ “page”: “/” }	{ “name”: “Chrome”, “os”: “Mac”, “resolution”: { “x”: 1440, “y”: 900 } }
pageview	2	{ “page”: “/” }	{ “name”: “Firefox”, “os”: “Windows”, “resolution”: { “x”: 1920, “y”: 1200 } }
pageview	1	{ “page”: “/account” }	{ “name”: “Chrome”, “os”: “Mac”, “resolution”: { “x”: 1440, “y”: 900 } }
purchase	5	{ “amount”: 10 }	{ “name”: “Firefox”, “os”: “Windows”, “resolution”: { “x”: 1024, “y”: 768 } }
purchase	15	{ “amount”: 200 }	{ “name”: “Firefox”, “os”: “Windows”, “resolution”: { “x”: 1280, “y”: 800 } }
purchase	15	{ “amount”: 500 }	{ “name”: “Firefox”, “os”: “Windows”, “resolution”: { “x”: 1280, “y”: 800 } }

使用聚集函数

json 操作可以和传统的聚集函数一期使用。下面示例统计浏览器类型：

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

browser	count
Chrome	2
Firefox	4

• 统计访问者：

  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
15	700
5	10

• 屏幕分辨率平均值

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

返回结果：

width	height
1397.33	894.67

总结

本文带你学习了PostgreSQL的jsonb类型，主要通过示例展示如何查询json的属性值，并和聚集函数一起使用。另外也可以对jsonb类型的属性进行增加、修改、删除等操作，读者可以查阅相关文档进一步学习。