PostgreSQL中jsonb类型字段操作

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"}' )

 暂时先记录这几个，后面再遇到再记录

• « 上一篇：ArrayList和LinkedList的区别
• » 下一篇：servlet的运行原理