文档章节

What can you do with PostgreSQL and JSON?

一号男嘉宾
 一号男嘉宾
发布于 2015/04/24 20:29
字数 813
阅读 62
收藏 0


PostgreSQL 9.2 added a native JSON data type, but didn’t add much else. You’ve got three options if you actually want to do something with it:

  1. Wait for PostgreSQL 9.3 (or use the beta)
  2. Use the plv8 extension. Valid option, but more DIY (you’ll have to define your own functions)
  3. Use the json_enhancements extension, which backports the new JSON functionality in 9.3 to 9.2

I wanted to use this stuff now, and I opted to go with option 3. I wrote a blog post which should help you get going if you want to go this route: adding json_enhancements to PostgreSQL 9.2.

So let’s assume you’re on either 9.3, or 9.2 with json_enhancements. What can you do? Lots! All the new JSON operators and functions are in the 9.3 documentation, so I’m going to run through some of the more fun things you can do along with a real-world use case.

Get started

Create a database to play about in:


createdb json_test
psql json_test




With some sample data:


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" } }');



Selecting

You can use the JSON operators to pull values out of JSON columns:


SELECT id, data->>'name' AS name FROM books; id | name ----+-----------------  1 | Book the First 2 | Book the Second 3 | Book the Third



The -> operator returns the original JSON type (which might be an object), whereas ->> returns text. You can use the -> to return a nested object and thus chain the operators:

SELECT id, data->'author'->>'first_name' as author_first_name FROM books; id | author_first_name ----+-------------------  1 | Bob 2 | Charles 3 | Jim



How cool is that?

Filtering

Of course, you can also select rows based on a value inside your JSON:


SELECT * FROM books WHERE data->>'name' = 'Book the First'; id | data ----+---------------------------------------------------------------------------------------  1 | '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }'



You can also find rows based on the value of a nested JSON object:


SELECT * FROM books WHERE data->'author'->>'first_name' = 'Charles'; id | data ----+---------------------------------------------------------------------------------------------  2 | '{ "name": "Book the Second", "author": { "first_name": "Charles", "last_name": "Xavier" } }'



Indexing

You can add indexes on any of these using PostgreSQL’s expression indexes, which means you can even add unique constraints based on your nested JSON data:


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.



Expression indexes are somewhat expensive to create, but once in place will make querying on any JSON property very fast.

A real world example

OK, let’s give this a go with a real life use case. Let’s say we’re tracking analytics, so we have an events table:


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



We’re going to store events in this table, like pageviews. Each event has properties, which could be anything (e.g. current page) and also sends information about the browser (like OS, screen resolution, etc). Both of these are completely free form and could change over time (as we think of extra stuff to track).

Let’s insert a couple of events:


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 } }' );



Hm, this is starting to remind me of MongoDB!

Collect some stats

Using the JSON operators, combined with traditional PostgreSQL aggregate functions, we can pull out whatever we want. You have the full might of an RDBMS at your disposal.

Browser usage?

SELECT browser->>'name' AS browser, count(browser) FROM events GROUP BY browser->>'name'; browser | count ---------+-------  Firefox | 3 Chrome | 2 




Total revenue per visitor?

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 




Average screen resolution?

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 




You’ve probably got the idea, so I’ll leave it here.



© 著作权归作者所有

共有 人打赏支持
一号男嘉宾
粉丝 133
博文 28
码字总数 40512
作品 0
成都
HTAP数据库 PostgreSQL 场景与性能测试之 46 - (OLTP) 大json字段的高并发更新

标签 PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试 背景 PostgreSQL是一个历史悠久的数据库,历史可以追溯到1973年,最早由2014计算机图灵奖得主,关系数据库的鼻祖MichaelStonebrake...

德哥
07/28
0
0
PostgreSQL是不是你的下一个JSON数据库?

根据Betteridge定律(任何头条的设问句可以用一个词来回答:不是),除非你的JSON数据很少修改,并且查询很多。 最新版的PostgreSQL添加更多对JSON的支持,我们曾经问过PostgreSQL是否可以替...

swingcoder
2015/08/09
0
0
PostgreSQL 相似文本检索与去重 - (银屑病怎么治?银屑病怎么治疗?银屑病怎么治疗好?银屑病怎么能治疗好?)

标签 PostgreSQL , 相似字符串 , 全文检索 , 去重 , 相似问题 , 医疗 , plr , plpython , madlib , 文本处理 背景 在云栖社区的问答区,有一位网友提到有一个问题: 解这个问题的思路 1. 首先...

德哥
04/18
0
0
PostgreSQL和MySQL

翻译来源:https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/ PostgreSQL和MySQL 之间有着根本的区别。在评估两个系统之间的差异和折衷之后,必须做出明智的决定。 我们已经提...

悟道之客
05/04
0
0
Postgres XL FAQ

Q. What does XL stand for? XL is short for eXtensible Lattice. It also connotes an extra large version of PostgreSQL, in this case across multiple systems. Q. Is this a “NoSQL......

javasql
2014/07/31
0
0

没有更多内容

加载失败,请刷新页面

加载更多

70.shell的函数 数组 告警系统需求分析

20.16/20.17 shell中的函数 20.18 shell中的数组 20.19 告警系统需求分析 20.16/20.17 shell中的函数: ~1. 函数就是把一段代码整理到了一个小单元中,并给这个小单元起一个名字,当用到这段...

王鑫linux
今天
0
0
分布式框架spring-session实现session一致性使用问题

前言:项目中使用到spring-session来缓存用户信息,保证服务之间session一致性,但是获取session信息为什么不能再服务层获取? 一、spring-session实现session一致性方式 用户每一次请求都会...

WALK_MAN
今天
5
0
C++ yield()与sleep_for()

C++11 标准库提供了yield()和sleep_for()两个方法。 (1)std::this_thread::yield(): 线程调用该方法时,主动让出CPU,并且不参与CPU的本次调度,从而让其他线程有机会运行。在后续的调度周...

yepanl
今天
4
0
Java并发编程实战(chapter_3)(线程池ThreadPoolExecutor源码分析)

这个系列一直没再写,很多原因,中间经历了换工作,熟悉项目,熟悉新团队等等一系列的事情。并发课题对于Java来说是一个又重要又难的一大块,除非气定神闲、精力满满,否则我本身是不敢随便写...

心中的理想乡
今天
31
0
shell学习之获取用户的输入命令read

在运行脚本的时候,命令行参数是可以传入参数,还有就是在脚本运行过程中需要用户输入参数,比如你想要在脚本运行时问个问题,并等待运行脚本的人来回答。bash shell为此提 供了read命令。 ...

woshixin
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部