Postgresql change an element of a JSON column

Asked

Viewed 55 times

1

Hello,

I’m trying to change a JSON column, but I want to change only one element, in this case, the "url". There are some urls that have the symbols ' ' and '$' at the beginning and end, as below:

json_avaliado = {"url": "^http://google.com$", "data": "2020-09-04T12:00:00.000Z"}

I would like to remove them, I managed to filter all the records that have it, but I could not change the element, I tried to use "jsonb_set" but I could not yet, follows what I tried so far:

update empresa 
set json_avaliado = jsonb_set(json_avaliado::jsonb, '{url}', substring(json_avaliado::json->>'url', 2, (length(json_avaliado::json->>'url')-2)))
where (left(json_avaliado::json->>'url', 1) = '^' or left(json_avaliado::json->>'url', 1) = '$')
  and (right(json_avaliado::json->>'url', 1) = '^' or right(json_avaliado::json->>'url', 1) = '$')

The mistake: inserir a descrição da imagem aqui

It works, I don’t know why it doesn’t understand that substring is a text:

set json_avaliado = jsonb_set(json_avaliado::jsonb, '{url}', 'teste')

Thank you!

database json postgresql sql-update jsonb

  • Note that it is indicating the second parameter (path) as unknown. Are you sure that your WHERE clause is correct? No parentheses missing (AND has priority over OR)?

  • Hi, yes, even removing the clause gives the error, I believe it is the substring that is impacting, because if I remove it and put a string qlqr, it works. :(

  • You’re confusing the functions subtring with substr. See in the manual: substring( string text [ FROM start integer ] [ FOR count integer ] ) → text and substr( string text, start integer [, count integer ] ) → text.

  • Replacing with 'substr' appears the same error :/

2 answers

0

I was able to solve the problem involving the substring within the function to_jsonb(), thus:

... 
set json_avaliado = jsonb_set(json_avaliado::jsonb, '{url}', to_jsonb(substring(json_avaliado::json->>'url', 2, (length(json_avaliado::json->>'url')-2))))
...

0

The third parameter of jsonb_set() expects a jsonb object, but vc is passing text.

inserir a descrição da imagem aqui

Cast a cast of substring(json_avaliado::json->>'url', 2, (length(json_avaliado::json->>'url')-2)) for jsonb q will fucionar.

(substring(json_avaliado::json->>'url', 2, (length(json_avaliado::json->>'url')-2)))::jsonb

Check out how to use function correctly in the documentation: https://www.postgresql.org/docs/9.5/functions-json.html

Browser other questions tagged database json postgresql sql-update jsonb

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