CODEWITHSUNDEEP
sqljsonpostgresqlsql-update
akt
akt

Reputation: 81

How to add a key value pair in Json type column in Postgres

I have a json type column(Status) in Postgres database(9.4.9). I want to add new key value pair for existing value of status. Example:

Existing Status:

"status": {
            "keysterStatus": "In Progress"
          }

After Adding Key value pair i want it to look like this

"status": {
            "provisioningStatus": "In Progress",
            "keysterStatus": "In Progress"
          }

I have been using repository save() method as of now to get this done but that is writing whole row and there is chance of concurrent read and write in case of multiple request. So wanted to get rid of save() method and go with column level update.

Upvotes: 3

Views: 6919

Answers (2)

Anupriyam Raj
Anupriyam Raj

Reputation: 136

UPDATE table_name SET column_name = jsonb_set(cast(column_name as jsonb), '{key}', '"value"', true) WHERE id = 'target_id';

This will add the key value pair in the json column if it doesn't exist already, if the key exist it will override the value of it.

Upvotes: 2

Roman Tkachuk
Roman Tkachuk

Reputation: 3266

First of all PG9.4 is obsolette and even unsopperted now. PG9.5 contains as json_set function:

SELECT jsonb_set(status::jsonb,
                 '{provisioningStatus}',
                 to_jsonb('In Progress'))::jsonb
  FROM ....;

as possibility to use concatenation || swith converting to jsonb and than back:

SELECT (status::jsonb || '{"provisioningStatus": "In Progress"}')::json
  FROM ....;

For PG9.4,if you know schema for json, uou can use json_populate_record/row_to_json :

SELECT (
         SELECT row_to_json(r)
           FROM (
                  SELECT r.*, 'In Progress' AS provisioningStatus
                    FROM json_populate_record(null::myrowtype, status) AS r
                ) AS r
       ) AS result
  FROM ....

Or you can use json_each_text:

SELECT (
         SELECT json_object_agg(key, value)
           FROM (
                  SELECT *
                    FROM json_each_text(status)
                   UNION ALL
                  SELECT 'provisioningStatus', 'In Progress'
                ) AS a
        ) AS result
   FROM ...

And probably the last (but ugly) method is just convert json to string, remove last '}', add "provisioningStatus": "In Progress"}' and convert back to json:

SELECT (substr(status::text, 1, length(status::text) - 1)
          || ', "provisioningStatus": "In Progress"}')::json
  FROM ...

Upvotes: 3

Related Questions