Postgresql CRUD operation on Single json data row

by Mohit Gupta   Last Updated June 06, 2015 22:02 PM

My requirement is that 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.

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

Thanks!!



Answers 2


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)
John Clark
John Clark
June 05, 2015 11:16 AM

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

Chris
Chris
July 31, 2015 01:40 AM

Related Questions


Is there a faster way to count JSONB tags?

Updated January 05, 2017 08:02 AM

Postgres multiple columns to json

Updated April 16, 2015 21:02 PM