数据库表json字段处理

PG数据库

字段attributes 存储数据为：{"aoi": "BL-B", "gloss": "BL-1"}

查询：SELECT * from check_sample WHERE "check_sample"."attributes"->>'aoi' = 'BL-B';

但更新的话，不像能非json字段那样更新，会报错如下：

---

UPDATE "public"."check_sample"

SET

"attributes"->>'aoi' = 'BL-B'

WHERE

"attributes"->>'aoi' = 'BLB'

> ERROR:  syntax error at or near "->>"

LINE 3:  "attributes"->>'aoi' = 'BL-B'

---

正确的Update更新sql为：

UPDATE "public"."check_sample"

SET "attributes" = (jsonb_set("attributes"::jsonb,'{aoi}','"BL-B"'::jsonb))

WHERE "attributes"->>'aoi' = 'BLB';

update 表名 set 列名 = (jsonb_set(列名::jsonb,'{key}','"value"'::jsonb)) where 条件 

SELECT

  ch.id,

ch."materialNo",

ch.line,

ch.station,

ch."checkDate",

chs."serialNumber",

  chs."attributes"

FROM

"check"  ch

LEFT JOIN check_sample chs on ch."id" = chs."checkId"

WHERE

ch.id IN ( SELECT DISTINCT("checkId") from check_sample WHERE "attributes" is not  NULL and  "check_sample"."attributes"->>'aoi'  in ('NG','Retest') )

--AND ch.line = 'CNC上盖'

ORDER BY chs."attributes"