Skip to content

Tag: jsonb

Format JSONB column by taking the text value from same jsonb coulmn

CREATE TABLE test(id serial, data jsonb); INSERT INTO test(data) values (‘dummydata-got-uploaded’); I need to correct the jsonb column value with below query. update test set data={“addDet”: data }::jsonb where id =1; ERROR: syntax error at or near “{” LINE 1: update test set data={“addDet”: data… Expected: id | data 1 | {“addDet”: ‘dummydata-got-uploaded’ } ` Thanks in advance. Answer you

Conditional update with jsonb_set()

I have a table in a Postgres 11.3 database with a jsonb column. Trying to update all objects inside a nested array name “iProps”. If the path {iProps -> value -> rules -> ao -> sc} is an object, then the path should be updated from an object to a string with the value {iProps -> value -> rules ->

how to use wildcard for a column jsonb type

I have a table (named profile) in my Postgres database, which has 3 columns: ID, Day, Ftfm_profile of type jsonb, I tried to extract the row where the profile name (ftfm_profile->’name’) begins with ‘LFBB’ ( sql: LFBB%) using the wildcard as following: the expected result: I can’t seem to find the solution, thanks for your help Answer One option unnests