dimanche 4 janvier 2015

Postgresql CRUD operation on Single json data row


Actually my requirement is that i have 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 .


Table structure is something like:


TaskDetails(Table name) 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


Now each entry of json data has array of records .


Like in first row with id =1 , Its having multiple records in array with each record having key's as (name,Country,Hobby) with respective values ,Now this array can be of 1000 to 10,000 size ,holding all these in 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 this data on this single row :


So that if i query for all data which is having name="Roy" i should get all the records having name = "Roy"


ie O/P 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 query for it 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 .


Thanks!!





Aucun commentaire:

Enregistrer un commentaire