PostgreSQL + JSON Data

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:

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

Table Schema

CREATE TABLE buku (
    id integer NOT NULL,
    data json
);

Insert Data

INSERT INTO buku VALUES (1, '{
    "tajuk": "Ayam Goreng",
    "penulis": {
        "nama_pena": "Bob Ayam",
        "nama_sebenar": "Bob Suka Ayam"
    },
    "info": {
        "diterbitkan": "20-03-2015",
        "sinopsis": "bob suka makan ayam, hari-hari dia makan ayam"
    }
}');

INSERT INTO buku VALUES (2, '{
    "tajuk": "Kambing Golek",
    "penulis": {
        "nama_pena": "Abu Al-Kambing",
        "nama_sebenar": "Abu"
    },
    "info": {
        "diterbitkan": "25-01-2015",
        "sinopsis": "abu suka tengok kambing, hari-hari beliau terbayang kambing golek"
    }
}');

INSERT INTO buku VALUES (3, '{
    "tajuk": "Cicak Terbang",
    "penulis": {
        "nama_pena": "Cicakman",
        "nama_sebenar": "Cicakak"
    },
    "info": {
        "diterbitkan": "29-01-2015",
        "sinopsis": "Cicak yang boleh terbang"
    }
}');

Selecting

SELECT id, data->>'tajuk' as tajuk FROM buku;
id |      name
----+-----------------
  1 | Ayam Goreng
  2 | Kambing Goreng
  3 | Cicak Terbang

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->'penulis'->>'nama_pena' as nama_pena FROM buku;

 id | nama_pena
----+-------------------
  1 | Bob Ayam
  2 | Abu Al-Kambing
  3 | Cicakman

Searching/Filtering You can select rows based on a value inside your JSON:

SELECT * FROM buku WHERE data->>'tajuk' = 'Cicak Terbang';

 id |                                         data
----+---------------------------------------------------------------------------------------
  1 | '{"tajuk": "Cicak Terbang","penulis": {"nama_pena": "Cicakman","nama_sebenar": "Cicakak"},"info": {"diterbitkan": "29-01-2015","sinopsis": "Cicak yang boleh terbang"}}'

Also with nasted JSON object

SELECT * FROM buku WHERE data->'penulis'->>'nama_pena' = 'Cicakman';

 id |                                         data
----+---------------------------------------------------------------------------------------
  1 | '{"tajuk": "Cicak Terbang","penulis": {"nama_pena": "Cicakman","nama_sebenar": "Cicakak"},"info": {"diterbitkan": "29-01-2015","sinopsis": "Cicak yang boleh terbang"}}' 

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 buku_penulis_nama_pena ON buku ((data->'penulis'->>'nama_pena'));

Cheers.