PostgreSQL JSONB类型及其操作

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类型的属性进行增加、修改、删除等操作，读者可以查阅相关文档进一步学习。

本文参考链接：https://blog.csdn.net/neweastsun/article/details/118711671