CODEWITHSUNDEEP
jsonpostgresqljsonbpostgresql-9.4postgresql-9.6
Fatih Doğan
Fatih Doğan

Reputation: 427

Postgresql update json data property

I created a field name is result and type is text. I just want to update 'lat' in column. When I use this query I get syntax error. How can I do?

The column data is

"{"lat":"48.00855","lng":"58.97342","referer":"https:\/\/abc.com\/index.php"}"

Query is 

update public.log set (result::json)->>'lat'=123 where id=6848202

Syntax error is 

ERROR:  syntax error at or near "::"

Upvotes: 13

Views: 30904

Answers (4)

jian
jian

Reputation: 4824

In PostgreSQL 13, You can:

update public.log set result = jsonb_set(result,'{lat}','"123"') where id=6848202;

Upvotes: 1

Gryu
Gryu

Reputation: 2189

I also tried to update json value in json type field, but couldn't find appropriate example. So I've connected to postgres DB using PgAdmin4, opened desired table and changed desired field's value, then looked at Query History to see what command it uses to change it.

So, finally, I've got the next simple python code for own purposes to update json field in postgres db:

import psycopg2

conn = psycopg2.connect(host='localhost', dbname='mydbname', user='myusername', password='mypass', port='5432')
cur = conn.cursor()
cur.execute("UPDATE public.mytable SET options = '{\"credentials\": \"required\", \"users\": [{\"name\": \"user1\", \"type\": \"string\"}]}'::json WHERE id = 8;")
cur.execute("COMMIT")

enter image description here

Upvotes: 0

klin
klin

Reputation: 121604

Use the jsonb concatenation operator (Postgres 9.5+):

update log
set result = result::jsonb || '{"lat":"123"}'
where id = 6848202

In Postgres 9.4 use json_each() and json_object_agg() (because jsonb_object_agg() does not exists in 9.4).

update log
set result = (
    select json_object_agg(key, case key when 'lat' then '123' else value end)
    from json_each(result)
    )
where id = 6848202

Both solutions assume that the json column is not null. If it does not contain the lat key, the first query will create it but the second will not.

Upvotes: 25

Ricardo Trindade
Ricardo Trindade

Reputation: 53

In case the column is still null, you can use coalesce. The answer is provided here: PostgreSQL 9.5 - update doesn't work when merging NULL with JSON

Upvotes: 0

Related Questions