postgresql 如何在json列中追加给定键的值?

tgabmvqs  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(136)

我有user_product_mapping存储每个用户的产品:

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)
);

字符串
样本值:

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


现在我想添加一个新的产品作为“笔记本电脑”到现有的user_id 2。
预期结果:

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


我试着补充,但面临一个问题:

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.


请你提出前进的方向。

vsdwdz23

vsdwdz231#

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

字符串
即使在缺少“产品”键、空值或空列表的情况下也能正常工作,但这是在给Pig涂口红。
它可以分解为提取文本值,将另一个项目连接到列表(安全地),并分配一个新 Package 的JSON值。请参阅:

  • 如何在Postgres SELECT中连接列?

在使用JSON时,data type jsonb会更实用,所以你可以使用jsonb_set()

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


更实际的是,使用JSON数组而不是文本值列表。

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


这将是更实际的Postgres数组的文本:text[],然而。

UPDATE ...
SET    product_info = product_info || 'laptop'


但你可能真正应该做的是一个适当规范化的多对多实现:

  • 如何在PostgreSQL中实现多对多关系?
  • 如何对JSONB类型的列执行更新操作

然后,您可以使用普通的INSERTDELETE添加和删除产品,而不会像上面所有的操作那样导致同一用户并发写入的问题。

相关问题