Update json object in a varchar column

Asked

Viewed 54 times

-1

Guys I’m having a problem at Postgres, I have a column that’s in format varchar but as a need I have to put a saved JSON object in it

{"value": "value", "value2": "value2"}

It is possible to update only the value2 for example?

sql database postgresql

  • Your question seems to have some problems and your experience here in Stack Overflow may not be the best because of this. We want you to do well here and get what you want, but for that we need you to do your part. Here are some guidelines that will help you: Stack Overflow Survival Guide in English.

1 answer

0


In the latest versions of Postgres, there are several functions for handling JSON.

But considering you need to use the type VARCHAR and update only a portion of JSON in the column, I suggest using Regular Expression.

See an example below:

SELECT REGEXP_REPLACE(
   '{"value": "value", "value2": "value2", "value3": "value3"}', 
   '(?<="value2": )(null|\d+(\.\d+)?|"([^"]*(\\")?)*")', 
   quote_ident('New Value'), 
   'g')

In the case of UPDATE, would look like this:

UPDATE tabela
SET coluna = 
   REGEXP_REPLACE(coluna, 
      '(?<="value2": )(null|\d+(\.\d+)?|"([^"]*(\\")?)*")', 
      quote_ident('New Value'), 
      'g')
WHERE id = 123456

The use of the function quote_identis only for quotation marks. If you are going to add a numerical value, just pass the direct literal.

Browser other questions tagged sql database postgresql

You are not signed in. Login or sign up in order to post.