- 站点信息
- 问题反馈
- 邮箱：[email protected]
- 吐槽一下
关注微信
©2020-2025 ioDraw All rights reserved,
Privacy Policy
1、查询jsonb字段不为null
SELECT ID, watcher :: json ->> 'processRouteWorkstations' AS workstations FROM
mo_mfg_order WHERE enabled = TRUE AND mfg_order_status_enum !=
'MfgOrderStatus.unReleased' AND watcher ->'processRouteWorkstations' is not
null AND deleted = FALSE
注意，会返回{}空json的行
2、查询json字段不为{}
SELECT ID, watcher :: json ->> 'processRouteWorkstations' AS workstations FROM
mo_mfg_order WHERE enabled = TRUE AND mfg_order_status_enum !=
'MfgOrderStatus.unReleased' AND watcher->'processRouteWorkstations' != '{}' AND
deleted = FALSE;
3、查询包含某key的数据，结果和is not null示例1相同
SELECT ID, watcher :: json ->> 'processRouteWorkstations' AS workstations FROM
mo_mfg_order WHERE enabled = TRUE AND mfg_order_status_enum !=
'MfgOrderStatus.unReleased' AND jsonb_exists(watcher,
'processRouteWorkstations') AND deleted = FALSE;
4、删除某个属性
UPDATE bc_process_route SET extra = jsonb_delete ( extra, 'nodes' ) WHERE ID =
1425995628128104449;
5、向json字段中添加属性
UPDATE bc_process_route SET extra = extra || '{"name":"penn"}' :: jsonb WHERE
ID = 1425995628128104449; UPDATE bc_process_route SET extra = extra ||
'{"age":10}' :: jsonb WHERE ID = 1425995628128104449;
6、也可以用上面的方式修改某个属性
UPDATE bc_process_route SET extra = extra || '{"name":"penn"}' :: jsonb WHERE
ID = 1425995628128104449;
7、查询包含json
SELECT ID , extra FROM bc_process_route WHERE jsonb_contains ( extra,
'{"name": "penn"}' )
8、查询包含于某个json内容
SELECT ID , extra FROM bc_process_route WHERE jsonb_contained ( extra,
'{"name": "penn", "age": 10, "address": "shanghai"}' )
暂时先记录这几个，后面再遇到再记录