PostgreSQL JSON SQL Update Statement

Question

I am testing out how JSON works in PostgreSQL 9.4 and I'm finding it to be really cool so far.

I'm stuck on one part though. I'm hoping it is possible to run a SQL UPDATE statement on JSON data.

What I was trying to see if I could do was have a Nested Set inside JSON data and Update the left and right when I add a comment.

My query is:

UPDATE
    comments
SET
    comment #>> '{right}' += 2
WHERE
    comment #>> '{post_id}' = '{$input['post_id']}'
    AND comment#>>'{right}' >= '{$parent->right}'

I do get an error:

Syntax error: 7 ERROR: syntax error at or near "#>>"
LINE 5: comment#>>'{right}' += 2

I've not been able to find a resource that tells me if it's possible to update an item inside the JSON.

Thank you

possible duplicate of [How do I modify fields inside the new PostgreSQL JSON datatype?](http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) — HaveNoDisplayName, Jul 13 '15 at 15:19

score 0 · Answer 1 · answered Jun 10 '21 at 13:26

Updating JSON Data

If the column in your table contains json data and you want to update this data, you can use the following structure:

UPDATE table_name SET column_name = '{"key" : value}'::jsonb 
WHERE column_name::jsonb @> '{“new_key” : new_value}'::jsonb;

Note: Usually @> is used as the "contains" operator.

PostgreSQL JSON SQL Update Statement

1 Answers1