postgres with json

postgres_json.sql

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

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




SELECT id, data->>'name' AS name FROM books;

SELECT id, data->'author'->>'first_name' AS author_first_name FROM books;

SELECT * FROM books WHERE data->>'name' = 'Book the First';

SELECT * FROM books WHERE data->'author'->>'first_name' = 'Charles';

CREATE UNIQUE INDEX books_author_first_name ON books ((data->'author'->>'first_name'));



SELECT name, setting
FROM pg_settings
WHERE category = 'File Locations';


--        name        |                                   setting
-- -------------------+-----------------------------------------------------------------------------
--  config_file       | /Users/rpietro/Library/Application Support/Postgres/var-9.3/postgresql.conf
--  data_directory    | /Users/rpietro/Library/Application Support/Postgres/var-9.3
--  external_pid_file |
--  hba_file          | /Users/rpietro/Library/Application Support/Postgres/var-9.3/pg_hba.conf
--  ident_file        | /Users/rpietro/Library/Application Support/Postgres/var-9.3/pg_ident.conf


createlang plpythonu json_test;

CREATE FUNCTION hello(name text)
	RETURNS text
AS $$
	return 'hello %s' % name
$$ LANGUAGE plppythonu;


CREATE DATABASE inbound OWNER rpietro;
psql inbound
CREATE TABLE mandrill ( id integer, data json );
INSERT INTO mandrill VALUES (1, '{ "email_address": "[email protected]", "name": { "first_name": "Ricardo", "last_name": "Pietrobon" }, "experiment": "simple email" }'); 
INSERT INTO mandrill VALUES (2, '{ "email_address": "[email protected]", "name": { "first_name": "Marcia", "last_name": "Pietrobon" }, "experiment": "simple email" }'); 
INSERT INTO mandrill VALUES (3, '{ "email_address": "[email protected]", "name": { "first_name": "Bruno", "last_name": "Melo" }, "experiment": "simple email" }'); 


SELECT id, data->>'email_address' AS email_address FROM mandrill;

SELECT id, data->'author'->>'first_name' AS author_first_name FROM books;

SELECT * FROM books WHERE data->>'name' = 'Book the First';

SELECT * FROM books WHERE data->'author'->>'first_name' = 'Charles';

CREATE UNIQUE INDEX books_author_first_name ON books ((data->'author'->>'first_name'));