CRUD operations on json data I have a large chunk of json data associated with each id and I have to somehow get the data for my front end by querying through that large chunk.

My TaskDetails table has a structure something like:

id | data
----------------
1  | [{"name":"Roy","Country":"USA","Hobby":"Swim"},
     {"name":"Roy","Country":"USA","Hobby":"Cricket"},
     {"name":"Anam","country":"Greece","Hobby":"Polo"}]
2  | [{"Address":"Church Street","Sex":"M"},
     {"Address":"Amsterdam","Sex":"F"},
     {"Address":"MG Road","Sex":"M"}]


where id is of type integer and data is of type json. Each entry of json data has an array of records.

In the row with id=1 each record has keys (name,Country,Hobby) with respective values. Now this array can be larger, perhaps 10,000 records, all held in a single json row. Something like :

1 | [{"name":"Roy","country":"USA","Hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","Hobby":"Polo"},
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"} ,
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"},
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"}]


Now I want to do CRUD operations for the data in this single row.

If I query for all data which is has name="Roy" I should get all the records having name = "Roy" and the output should be:

{"name":"Roy","country":"USA","Hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"}


I am not able to form a query for this. I am trying with something like:

select * 
from TaskDetails 
where data-&gt;0-&gt;&gt;'name'='Roy'


but it will not give me all the records. How should I query? I cannot use jsonb since our prod PostgreSQL DB is currently 9.3.4.
Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb is your data type.

9.3 json
https://gist.github.com/matheusoliveira/9488951

(and while jsonb is not an option for Michael, for those who are using 9.4, there is a jsonb variant: https://gist.github.com/inindev/2219dff96851928c2282)

Operations Provided:

postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
        json_append        
---------------------------
 {"a":1,"b":2,"c":3,"a":4}
(1 row)

postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
 json_delete 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
 json_update 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
     json_merge      
---------------------
 {"b":2,"c":3,"a":4}
(1 row)