PostgreSQL ve JSON?

test veritabanı oluşturalım:

createdb json_testpsql json_test

test datası:

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

 Select:

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 

Filtreleme:

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

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

Index Oluşturma:

 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.

Gerçek hayattan örnek

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

 Browser kullanımı?
SELECT browser->>'name' AS browser, count(browser)FROM eventsGROUP BY browser->>'name'; browser | count---------+------- Firefox | 3 Chrome | 2
Ziyaretçi başına toplam gelir?

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


Ortalama ekran çözünürlüğü?

SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width, AVG(CAST(browser->'resolution'->>'y' AS integer)) AS heightFROM events; width | height-----------------------+---------------------- 1397.3333333333333333 | 894.6666666666666667 

@http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/