How to perform update operations on columns of type JSON in Postgres 9.3?

Question

As an example, I have this basic table structure:

CREATE TABLE test (id serial, info json);
INSERT INTO test(info)
   VALUES ('{
             "dept":[
                     {
                      "name":"abc",
                      "desc":"hello",
                      "enabled":false
                     },
                     {
                      "name":"xyz",
                      "desc":"hello",
                      "enabled":false
                     }
                    ]
            }');

I want to update and set "enabled":true where "name" is "abc".

Hope this Link Will Help you. https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype — idiotduffer, Jan 17 '18 at 05:45
JSONB datatype is not available in 9.3 , So I need to update it without JSONB. — Narender Pal, Jan 17 '18 at 05:53

score 1 · Answer 1 · edited Jan 17 '18 at 08:05

PostgreSQL 9.3 only has functions to create JSON, but no functions to use or modify it. I've already faced it before and I used the 2 trick below

Cast it into text and use function regexp_replace. See the query below
```
SELECT info, 
       regexp_replace(info::text,
                      '("name":"abc"[^}]+)"enabled":false',
                      E'\\1"enabled":true'
                     )::json 
FROM test;
```
More explanation: the \\1 represents the characters matched between ( and ) in the search string. You can find more information here.
Newer versions of PostgreSQL have functions to modify JSON. So install a small instance of PostgreSQL in a newer version and use the features “foreign table” or “dblink” to update the data with the primary key. For the functions to update json and jsonb you can get more here.

Hopefully it'll help you in this case.

How to perform update operations on columns of type JSON in Postgres 9.3?

1 Answers1