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

CodePudding user response：

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;

CodePudding user response：

Solution for your question 1.

Updating the mobile1 json value in the info json field :

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

Solution for your question 2.

Removing the mobile2 key/value pair from the info json field :

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

Deleting the entire row from the contacts table :

delete from contacts where id=5

You should read carefully the manual Chapter 6. Data Manipulation and 9.16. JSON Functions and Operators