Postgresql update json data property

Question

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

Postgres version is 9.6 – Fatih Doğan Nov 23 '17 at 11:49 — Fatih Doğan, Nov 23 '17 at 11:49
Any thoughts on how we can achieve this in postgres 9.4? – Abdul23 Mar 07 '19 at 10:37 — Abdul23, Mar 07 '19 at 10:37

klin · Accepted Answer · 2019-09-28T15:20:50.070

23

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.

edited Sep 28 '19 at 15:20

answered Nov 23 '17 at 11:50

klin

112,967
15
204
232

In case somebody's wondering how to do this with data coming out of an already existing column, y'all can leverage `json_build_object` function to do this. `result::jsonb || json_build_object('key', column)` – Joan Gil Oct 12 '21 at 16:23

score 1 · Answer 2 · answered Sep 30 '21 at 04:39

1

In PostgreSQL 13, You can:

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

answered Sep 30 '21 at 04:39

jian

4,119
1
17
32

score 0 · Answer 3 · answered Mar 20 '18 at 15:57

0

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

answered Mar 20 '18 at 15:57

Ricardo Trindade

53
6

score 0 · Answer 4 · answered Feb 03 '21 at 18:42

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

Postgresql update json data property

4 Answers4