Skip to main content
 首页 » 数据库

PostgreSQL JSONB类型及其操作

2022年07月19日149lyhabc

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