Postgresql - JSONB Operations

JSONB_POSTGRESQL_PARTIAL_UPDATE.sql

select * from tabela

/* UPDATE */

SELECT CONTEUDO->'ficha' from tabela WHERE ID=? FOR UPDATE /* BLOQUEIA A LINHA PARA ATUALIZACAO */; 

/* OBTEM O INDICE * DO ITEM DA FICHA */

/* FAZ O JSON_B_SET */
UPDATE tabela SET CONTEUDO = JSONB_SET(CONTEUDO, '{ficha,?}'::text, ?::jsonb), ULTIMA_ATUALIZACAO_CONTEUDO=NOW() WHERE ID=? and (conteudo->'ULTIMA_ATUALIZACAO_CONTEUDO' IS NULL OR conteudo->'ULTIMA_ATUALIZACAO_CONTEUDO'=?);

COMMIT;

select coalesce(null, null, 1)
/* INSERT */

SELECT CONTEUDO->'ficha' from tabela WHERE ID=? FOR UPDATE;

UPDATE tabela set CONTEUDO = JSONB_SET(CONTEDO, '{ficha}', coalesce(CONTEUDO->'ficha', '[]'::jsonb) || ?::jsonb), ULTIMA_ATUALIZACAO_CONTEUDO=NOW() WHERE ID=? and (conteudo->'ULTIMA_ATUALIZACAO_CONTEUDO' IS NULL OR conteudo->'ULTIMA_ATUALIZACAO_CONTEUDO'=?);

COMMIT;


/* DELETE */

SELECT CONTEUDO->'ficha' from tabela WHERE ID=? FOR UPDATE /* BLOQUEIA A LINHA PARA ATUALIZACAO */; 

/* OBTEM O ELEMENTO A SER REMOVIDO */

/* FAZ O JSON_B_SET REMOVENDO O ELEMENTO*/
UPDATE tabela SET CONTEUDO = JSONB_SET(CONTEUDO, '{ficha,?}'::text, CONTEUDO->'ficha' - ?::jsonb), ULTIMA_ATUALIZACAO_CONTEUDO=NOW() WHERE ID=? and (conteudo->'ULTIMA_ATUALIZACAO_CONTEUDO' IS NULL OR conteudo->'ULTIMA_ATUALIZACAO_CONTEUDO'=?);

/* CERTIFICA-SE QUE CONTEUDO != NULL E QUE ELEMENTOS EM Conteudo->ficha foi decrementado */
COMMIT;




select coalesce(conteudo->'ULTIMA_ATUALIZACAO', '[]'::jsonb) from tabela;


/* JUSTIFICATIVA PARA ESTE MODELO */

/*

* descrever operações de crud com jsonb_set totalmente em SQL além de não ser muito legível, deixando o código de difícil manutenção,
também pode levar a erros que provoquem a perda de dados, como descrito em 

*/


OU

/* inserindo um item em uma ficha  */
UPDATE declaracao 
SET CONTEUDO = (
	SELECT 
		/* FICHA IS NULL */ CASE WHEN  ('{"debitos": []}'::jsonb)->'pagamentos' ISnull THEN ('{"debitos": []}'::jsonb) || '{"pagamentos": [ {"codigo": 1}]}'::jsonb
		/* FICHA JÁ EXISTE */ ELSE jsonb_insert('{"debitos": []}', '{pagamentos,-1}', jsonb '{ "codigo": 1}', true)
	END
)
WHERE CPF=? and EXERCICIO=?;


/* atualizando um item em uma ficha */

UPDATE declaracao
SET CONTEUDO = JSONB_SET(
				CONTEUDO, 
				'{pagamentos}':text[], 
				(((CONTEUDO->'pagamentos' - (SELECT i FROM generate_series(0,json_array_length(CONTEUDO->'pagamentos')-1) AS i WHERE CONTEUDO->'pagamentos'->i->>'id'='gid')))::jsonb
				|| item::jsonb)) 
} WHERE CPF=? and EXERCICIO=?

OU

UPDATE declaracao
SET CONTEUDO = JSONB_SET(
	CONTEUDO,
	CONCAT('{pagamentos,', (SELECT i FROM generate_series(0,json_array_length(CONTEUDO->'pagamentos')-1) AS i WHERE CONTEUDO->'pagamentos'->i->>'id'='gid'),'}'),
	?::jsonb
)
WHERE CPF=? and EXERCICIO=?

select json_array_length(('{"pagamentos": [{ "id": "132", "nome": "abner"}]}'::json)->'pagamentos')

select i from generate_series(0, json_array_length('{"pagamentos": [{},{"id": "111"},{ "id": "132", "nome": "abner"}]}'::json->'pagamentos')-1) AS I WHERE '{"pagamentos": [{},{}, { "id": "132", "nome": "abner"}, { "id": "222", "nome": "cris"}]}'::jsonb->'pagamentos'->i->>'id' = '132'

select jsonb_set(
		'{"pagamentos": [{ "id": "132", "nome": "abner"}]}'::jsonb,
		CONCAT('{pagamentos,', coalesce((SELECT i FROM generate_series(0,json_array_length('{"pagamentos": [{ "id": "1322", "nome": "abner"}]}'::json->'pagamentos')-1) AS i WHERE '{"pagamentos": [{ "id": "1322", "nome": "abner"}]}'::jsonb->'pagamentos'->i->>'id' = '132'),-1),'}')::text[],
		'{ "id": "132", "nome": "abner silva de oliveira" }'::jsonb
		) WHERE COALESCE((SELECT i FROM generate_series(0,json_array_length('{"pagamentos": [{ "id": "1322", "nome": "abner"}]}'::json->'pagamentos')-1) AS i WHERE '{"pagamentos": [{ "id": "1322", "nome": "abner"}]}'::jsonb->'pagamentos'->i->>'id' = '132'),-1) >=0

select jsonb_set('{"a": 1}'::jsonb, concat('{', 'a', '}')::text[], '"ABNER"'::jsonb)

SELECT CONCAT('1','B',2)
/* 

REF: 

* CRUD: https://idalko.com/crud-operations-postgres-jsonb/

* ROW LOCK: https://www.postgresql.org/docs/9.4/static/explicit-locking.html

* FUNCTIONS JSON: https://www.postgresql.org/docs/9.5/static/functions-json.html

* https://blog.2ndquadrant.com/jsonb-and-postgresql-9-5-with-even-more-powerful-tools/



*/