[FIXED] How To Add,Update And Delete From Json DataType On Postgresql?

Issue

This is my table on PostgreSQL with name contacts:

1. I want to edit mobile1 value with this sql query:

update contacts->info set mobile1 = JSON_SET(mobile1, "123456") where id=5

but that says :: ERROR: syntax error at or near "->"

2. and when i want to delete or add a value with this sql query:

delete orders->info->mobile2 where id=5

syntax error at or near "orders"

3. Or ADD

update orders->info set mobile3 = JSON_SET(mobile3, "123456") where id=5

syntax error at or near "->"

What's my syntax problem? and how can I do add, update and delete on my json datatype table on PostgreSQL

Solution

According to Postgres document for insert, update, or delete you should use JSONB operation or function.

Demo

1. Update scenario:

update contacts
set info = jsonb_set(info::jsonb, '{mobile,mobile1}', '"123456"')::json
where id = 5;

2. Delete scenario:

update contacts
set info = (info::jsonb #- '{mobile,mobile2}')::json
where id = 5;

2. Add scenario:

update contacts
set info = jsonb_set(info::jsonb, '{mobile,mobile3}', '"123456"')::json
where id = 5;

Answered By - Pooya

Answer Checked By - Cary Denson (PHPFixing Admin)