How to append to the value of a given key in a json column?-CodePudding

I have user_product_mapping storing product per user:

DROP TABLE IF EXISTS user_product_mapping;
CREATE TABLE user_product_mapping 
(
    id SERIAL NOT NULL PRIMARY KEY,
    user_id INT,
    product_info json NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(user_id)
);

Sample values:

INSERT INTO user_product_mapping (user_id, product_info)
VALUES
(1, '{"product": "laptop,mobile"}'),
(2, '{"product": "charger"}'),
(3, '{"product": "mobile,mouse,charger"}')
;

Now I want to add a new product as 'laptop' to existing user_id 2.
Expected result:

user_id | product_info
---------------------------------------
      2 | {"product": "charger,laptop"}

I have tried to append but face an issue:

UPDATE user_product_mapping
SET product_info = product_info || '{"laptop"}'
WHERE user_id = 2;

Error: 
ERROR:  column "product_info" is of type json but expression is of type text
LINE 2: SET product_info = product_info || '{"123e4567-e89b-12d3-a45...
                           ^
HINT:  You will need to rewrite or cast the expression.

Can you please suggest the way forward.

CodePudding user response：

UPDATE user_product_mapping
SET    product_info = json_build_object('product', concat_ws(',', NULLIF(product_info->>'product', ''), 'laptop'))
WHERE  user_id = 2;

Even works properly with a missing 'product' key, one with a null value, or an empty list. But it's putting lipstick on a pig.

It burns down to extracting the text value, concatenating another item to the list (safely), and assigning a newly wrapped JSON value. See:

How to concatenate columns in a Postgres SELECT?

While using JSON, the data type jsonb would be more practical, so you can use jsonb_set():

SET    product_info = jsonb_set (product_info, '{product}', to_jsonb(concat_ws(',', NULLIF(product_info->>'product', ''), 'laptop')))

More practical, yet, with a JSON array instead of that list of text values.

SET    product_info = product_info || jsonb '"laptop"'

And that would be more practical as Postgres array of text: text[], yet.

SET    product_info = product_info || 'laptop'

But what you probably really should do is a properly normalized many-to-many implementation:

Then you add and remove products with a plain INSERT or DELETE, not causing problems with concurrent writes for the same user like all of the above will.