[大数据]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"}' )

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