patternsqlMinor

CRUD operations on json data

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

jsondataoperationscrud

Problem

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"

Solution

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)

Code Snippets

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)

Context

StackExchange Database Administrators Q#87516, answer score: 2

Revisions (0)

No revisions yet.