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