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

Answer 1

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.