Menu
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 "::"
750
To update JSON data in a table: Query and retrieve the JSON data from a JSON column. Modify the contents of the JSON column. To push the updated column value back into the table, issue the UPDATE SQL statement and specify the JSON2BSON function.
There are two ways to accomplish this: Simply concatenating the new key/value pair: update the_table set attr = attr || '{"is_default": false}'; This works because when concatenating two jsonb values, existing keys will be overwritten.
Postgres offers a jsonb_set function for updating JSON fields. The second parameter path defines, which property you want to update. To update items in an array, you can use an index-based approach. To update the first entry in the items array in the example above, a path woud look like this: {items, 0, customerId} .
JSONB stands for “JSON Binary” or “JSON better” depending on whom you ask. It is a decomposed binary format to store JSON. JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.
In PostgreSQL 13, You can:
update public.log set result = jsonb_set(result,'{lat}','"123"') where id=6848202;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
