CODEWITHSUNDEEP
sqljsonpostgresql
Tal Levi
Tal Levi

Reputation: 825

How to replace a string value with an object inside a column the contains json object

I'm using PostgreSQL and I have a table that has a text column named additional_info. This columns on this table currently contains a JSON object as below:

{
    "dbServiceAccount": {
        "aggregationMode": "DbServiceAccount",
        "destinationIp": "10.10.10.29",
        "db": "xe",
        "dbType": "Oracle",
        "dbUser": "system"
    },
    "clients": [{
        "user": "user5"
    }, {
        "user": "user4"
    }]
}

I want to replace the value of the key 'dbServiceAccount' with an object. The object should have a key 'name' that holds the original string value, like below:

{
    "dbServiceAccount": {
        "aggregationMode": {
            "name": "DbServiceAccount"
        },
        "destinationIp": "10.10.10.29",
        "db": "xe",
        "dbType": "Oracle",
        "dbUser": "system"
    },
    "clients": [{
        "user": "user5"
    }, {
        "user": "user4"
    }]
}

How can I do this?

Upvotes: 0

Views: 302

Answers (2)

Tal Levi
Tal Levi

Reputation: 825

I finally solved this:

WITH aggregationMode AS (

   select additional_info::json ->'dbServiceAccount'  -> 'aggregationMode' as aggregationMode from incidents."groups" g where id in ('3085875798') 
)

UPDATE 
   incidents."groups" g 
SET 
   additional_info = REPLACE(additional_info,CONCAT  ('"aggregationMode":"', (select * from aggregationMode), '"'),
   CONCAT  ('"aggregationMode": {"name": "', (select * from aggregationMode), '"}'))
where id = '111'

Upvotes: 0

Stefanov.sm
Stefanov.sm

Reputation: 13049

This can be done using jsonb_set.

with t(oldj) as
(
 select '{
  "dbServiceAccount": {
        "aggregationMode": "DbServiceAccount",
        "destinationIp": "10.10.10.29",
        "db": "xe",
        "dbType": "Oracle",
        "dbUser": "system"
  },
  "clients": [{"user": "user5"}, {"user": "user4"}]
 }'::jsonb
) -- the original JSONB object
select jsonb_set
(
  oldj,
  array['dbServiceAccount', 'aggregationMode'], 
  jsonb_build_object('name', oldj -> 'dbServiceAccount' ->> 'aggregationMode')
) as newj -- the resulting JSONB object
from t;

/* result:
{
  "clients": [
    {
      "user": "user5"
    },
    {
      "user": "user4"
    }
  ],
  "dbServiceAccount": {
    "db": "xe",
    "dbType": "Oracle",
    "dbUser": "system",
    "destinationIp": "10.10.10.29",
    "aggregationMode": {
      "name": "DbServiceAccount"
    }
  }
}
*/

Upvotes: 1

Related Questions