@H_301_0@ Postgresql 9.2 added a nativeJSONdata type,but didn’t add much else. You’ve got three options if you actually want to do something with it:
- Wait for Postgresql 9.3 (or use the beta)
- Use theplv8extension. Valid option,but more DIY (you’ll have to define your own functions)
- Use thejson_enhancementsextension,which backports the new JSON functionality in 9.3 to 9.2
Get started
@H_301_0@ Create a database to play about in: @H_301_0@createdb json_test psql json_test
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
@H_301_0@ You can use the JSON operators to pull values out of JSON columns: @H_301_0@SELECT id,data->>'name' AS name FROM books; id | name ----+----------------- 1 | Book the First 2 | Book the Second 3 | Book the Third
SELECT id,data->'author'->>'first_name' as author_first_name FROM books; id | author_first_name ----+------------------- 1 | Bob 2 | Charles 3 | Jim
Filtering
@H_301_0@ Of course,you can also select rows based on a value inside your JSON: @H_301_0@SELECT * FROM books WHERE data->>'name' = 'Book the First'; id | data ----+--------------------------------------------------------------------------------------- 1 | '{ "name": "Book the First","last_name": "White" } }'
SELECT * FROM books WHERE data->'author'->>'first_name' = 'Charles'; id | data ----+--------------------------------------------------------------------------------------------- 2 | '{ "name": "Book the Second","last_name": "Xavier" } }'
Indexing
@H_301_0@ You can add indexes on any of these using Postgresql’sexpression indexes,which means you can even add unique constraints based on your nested JSON data: @H_301_0@CREATE UNIQUE INDEX books_author_first_name ON books ((data->'author'->>'first_name')); INSERT INTO books VALUES (4,'{ "name": "Book the Fourth","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.
A real world example
@H_301_0@ OK,let’s give this a go with a real life use case. Let’s say we’re tracking analytics,so we have aneventstable: @H_301_0@CREATE TABLE events ( name varchar(200),visitor_id varchar(200),properties json,browser json );
INSERT INTO events VALUES ( 'pageview','1','{ "page": "/" }','{ "name": "Chrome","os": "Mac","resolution": { "x": 1440,"y": 900 } }' ); INSERT INTO events VALUES ( 'pageview','2','{ "name": "Firefox","os": "Windows","resolution": { "x": 1920,"y": 1200 } }' ); INSERT INTO events VALUES ( 'pageview','{ "page": "/account" }',"y": 900 } }' ); INSERT INTO events VALUES ( 'purchase','5','{ "amount": 10 }',"resolution": { "x": 1024,"y": 768 } }' ); INSERT INTO events VALUES ( 'purchase','15','{ "amount": 200 }',"resolution": { "x": 1280,"y": 800 } }' ); INSERT INTO events VALUES ( 'purchase','{ "amount": 500 }',"y": 800 } }' );
Collect some stats
@H_301_0@ Using the JSON operators,combined with traditional Postgresqlaggregate functions,we can pull out whatever we want. You have the full might of an RDBMS at your disposal. @H_301_0@ Browser usage? @H_301_0@SELECT browser->>'name' AS browser,count(browser) FROM events GROUP BY browser->>'name'; browser | count ---------+------- Firefox | 3 Chrome | 2
@H_301_0@ Total revenue per visitor? @H_301_0@
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
@H_301_0@ Average screen resolution? @H_301_0@
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
@H_301_0@ You’ve probably got the idea,so I’ll leave it here.