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.

  • 2
    In 9.3 you'll have to do this client side or with a procedural language like PL/V8, PL/Python, PL/Perl, etc. – Craig Ringer Jan 4 '15 at 13:31
  • 2
    Presumably there is a good reason why this data is in json at all rather than in a table? – Jack Douglas Jun 6 '15 at 12:17
  • 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. – Jack Douglas Jun 6 '15 at 15:27

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)
  • 1
    It might be good to relegate the 9.4 info to a footnote as the OP says "I cannot use jsonb..." – Jack Douglas Jun 6 '15 at 15:28

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.