CRUD operations on json data

Question

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->0->>'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.

• Craig Ringer: In 9.3 you'll have to do this client side or with a procedural language like PL/V8, PL/Python, PL/Perl, etc.
• Jack Douglas: Presumably there is a good reason why this data is in json at all rather than in a table?
• Jack Douglas: also, are you actually doing CRUD or just querying? The answer below offers a way of updating json but not querying the way you want.

Answer 1

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)

• Jack Douglas: It might be good to relegate the 9.4 info to a footnote as the OP says "I cannot use jsonb..."

Answer 2

I can't build a full fledged test just right now, because I've gotta hit the road. Just note that this code is as of right now untested, but you can easily give it a shot since you already have the data tables. ;)

Using JSON + unnest() + CTEs

It looks like to me, from your description, that a data entry is an array of JSON. I'm just making this clear because you earlier stated that data is a JSON, but your data sample seems to conflict.

In this case, why not use a simple Common Table Expression (the WITH keyword) along with unnest() to extract the relevant JSONs as rows, and then perform your query?

WITH extracted_jsons AS (
 SELECT unnest(data) AS json_field FROM TaskDetails
 WHERE id = *desired_ids*
)
SELECT * FROM extracted_jsons
WHERE json_field -> 'name' = 'Roy';

Please test this, and see if my guess is correct. Sorry for any errors, but I've got to go sit in some traffic now. :P