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

Question

This is my table on PostgreSQL with name contacts:

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

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"

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

Unrelated but this would be much better and easier with a normalized data design. — Stefanov.sm
– Stefanov.sm, Commented Dec 14, 2021 at 7:37

Pooya · Accepted Answer · 2021-12-14 07:35:40Z

1

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

Demo

Update scenario:

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

Delete scenario:

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

Add scenario:

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

answered Dec 14, 2021 at 7:35

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amin Arjmand - امین ارجمند Over a year ago

thanks all of above worked, how can i add multiple mobile numbers on 1 query , add mobile4,mobile5,mobile6 ?

Pooya Over a year ago

You can use dbfiddle.uk/… if needed add multiple keys

Edouard · Accepted Answer · 2021-12-14 07:47:55Z

1

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

edited Dec 14, 2021 at 7:47

answered Dec 14, 2021 at 7:28

Edouard

7,1291 gold badge11 silver badges24 bronze badges

2 Comments

Amin Arjmand - امین ارجمند Over a year ago

thanks, your solution for question 1 this error appeard : ERROR: column "123456" does not exist for question 2 that worked@ and for question 3 how can I add another on my mobiles for example mobile3=987456 ?

Edouard Over a year ago

Yes indeed I copied / pasted your piece of code with double quotes instead of simple quotes. I've updated my answer.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related