JSONB Postgres

README.md

JSONB Postgres

Snippets para auxiliar a criação de queries com JSONB no Postgres

Select

Pegar o JSON de uma imagem:

SELECT dados->'imagens' 
FROM produto p 
CROSS JOIN jsonb_array_elements(p.dados->'imagens') WITH ORDINALITY i
WHERE 
	idproduto = 5
	AND (i.value->>'url')::text LIKE 'minha-imagem.jpg'

Verificar se um valor (int) existe no array

SELECT *
FROM campanha c
WHERE 
  c.idproduto @> jsonb_build_array(:idproduto) -- jsonb_build_array é necessário pois o valor é um inteiro
  -- Se fosse uma string seria só chamar assim: c.idproduto @> '1'

---

Insert

Adicionar um JSON de imagem

UPDATE produto 
SET dados = jsonb_set(
    dados, -- alvo do update
    '{imagens}'::TEXT[], -- campo que vai alterar
    dados->'imagens' || '{ 
        "url": "https://placehold.it/600x600",
        "principal": false,
        "thumbnails": null
    }' -- Novo valor - concatenamos o valor antigo com o novo valor para adicionar no array JSONB
)
WHERE idproduto = 1

---

Update

Alterar o JSON de uma imagem

UPDATE produto
SET dados = jsonb_set(
    dados, -- alvo do update
    '{imagens}'::text[], -- campo que vai alterar
    COALESCE(
        ((dados->'imagens') - ( 
            -- Buscar o índice do item que queremos buscar e removê-lo
            SELECT i FROM generate_series(0, jsonb_array_length(dados->'imagens')-1) AS i
            WHERE (dados->'imagens'->i->>'url' = 'https://placehold.it/800x800')
        ))::jsonb || '{
            "url": "https://placehold.it/600x600",
            "principal": true,
            "thumbnails": null
        }', -- Adicionar o novo elemento no final
        dados->'imagens'
    )
)
WHERE 
    idproduto = 1
    AND EXISTS ( SELECT 1 FROM jsonb_array_elements(dados->'imagens') img WHERE (img->>'url')::text = 'https://placehold.it/800x800' )

Alterar um atributo da imagem (Tornar imagem principal)

UPDATE produto
SET dados = jsonb_set(
    dados, -- alvo do update
    '{imagens}'::text[], -- campo que vai alterar
    COALESCE(
        TO_JSONB(ARRAY(
            -- Remover o atributo "principal" e concatenar com um novo valor
            SELECT (img.value - 'principal') || (
                -- Montar JSON { "principal": true } ou { "principal": false }
                '{ "principal": ' || CASE WHEN img->>'url' = 'https://placehold.it/800x800' THEN 'true' ELSE 'false' END || ' }'
            )::JSONB
            FROM JSONB_ARRAY_ELEMENTS(dados->'imagens') img
        )),
        dados->'imagens' -- COALESCE com o valor antigo pra evitar setar o campo inteiro como 'null'
    )
)
WHERE 
    idproduto = 1
    -- Essa condição repetida aqui serve pra retornar "0 linhas atualizadas" se a condição não for verdadeira
    AND EXISTS ( SELECT 1 FROM jsonb_array_elements(dados->'imagens') img WHERE (img->>'url')::text = 'https://placehold.it/800x800' )

Alterar o formato do valor da característica ("1.123,45" para "123.45")

UPDATE produto
SET dados = jsonb_set(
    dados, -- alvo do update
    '{caracteristicas}'::text[], -- campo que vai alterar
    COALESCE(
        TO_JSONB(ARRAY(
            SELECT 
            CASE WHEN (c->>'tipovalor' = 'float') THEN
	            (c.value - 'valor') || (
	                '{ "valor": "' || (REPLACE(REPLACE(c->>'valor', '.', ''), ',', '.')) || '" }'
	            )::JSONB
            ELSE (c.value) END
            FROM JSONB_ARRAY_ELEMENTS(dados->'caracteristicas') c
	    /* 
	    	Importante não usar "WHERE" aqui, e sim "CASE" no SELECT, caso contrário
	    	serão removidos do array os elementos que não cumprem a condição
	    */
        )),
        dados->'caracteristicas' -- COALESCE com o valor antigo pra evitar setar o campo inteiro como 'null'
    )
)
WHERE idproduto <> 1 AND idproduto <> 12
RETURNING *

---

Delete

Remover uma imagem

UPDATE produto
SET dados = jsonb_set(
    dados,
    '{imagens}'::text[],
    COALESCE(
        ((dados->'imagens') - (
            SELECT i FROM generate_series(0, jsonb_array_length(dados->'imagens')-1) AS i
            WHERE (dados->'imagens'->i->>'url' = 'https://placehold.it/800x800')
        ))::jsonb,
        dados->'imagens'
    )
)
WHERE 
    idproduto = 1
    AND EXISTS ( SELECT 1 FROM jsonb_array_elements(dados->'imagens') img WHERE (img->>'url')::text = 'https://placehold.it/800x800' )