How to change JSON key:value pair using UPDATE query?

Question

I have JSON object in an SQLite table column and need to change the value of a key:

column
{"foo": "bar", "bar2": "foo2"}

I want to get:

column
{"foo": "changed_value", "bar2": "foo2"}

Can I do it with an SQL query? If not, how to do it in Node.js with a minimal amount of code?

Answer 1

You need the function json_replace():

UPDATE tablename
SET col = json_replace(col, '$.foo', 'changed_value')

Change tablename and col to the names of the table and the column that you have.

Probably you also need a WHERE clause for the actual row(s) that you want to update.

See the demo.