Início / coding / Perguntas / 79577050
Accepted
Thomas
Asked: 2025-04-16 19:21:23 +0800 CST

Atualizar tabela de acordo com JSON/JSONB

  • 772

Eu tenho uma tabela books:

Nome da coluna Tipo
eu ia int (PK)
nome texto
nb_pages int

E uma mesa book_modifications:

Nome da coluna Tipo
eu ia int (PK)
dados_antigos jsonb
novos_dados jsonb
id_do_livro int (FK)

Agora vamos imaginar que tenho uma linha na minha bookstabela como esta:

id: 15,
name: "name-1",
nb_pages: 120

E uma linha para inserir na minha book_modificationstabela assim:

id: 7,
old_data: {name: "name-1", nb_pages: 120},
new_data: {name: "new-name-1"},
book_id: 15

Minha pergunta é:

Como posso atualizar minha bookstabela com a new_datacoluna da minha book_modificationstabela no SQL ou PostgreSQL?

Atualmente encontrei esta maneira:

create or replace function on_book_modifications_insert () 
returns trigger 
language plpgsql 
as $$
begin

-- Here (new_data only contains the 'name' field)
UPDATE books 
SET name = new.new_data -> 'name', nb_pages = new.new_data -> 'nb_pages' 
WHERE id = new.book_id;

return new;

end;
$$

Neste caso, a consulta SQL acima será atualizada nb_pagespara nullporque não está presente em new_data(o que gerará um erro), enquanto eu gostaria de deixar nb_pagesem 120 e atualizar apenas os campos presentes em new_data.

Para INSERT, vi que json_objectexiste, mas não consigo encontrá-lo para UPDATE.

sql

1 respostas

  1. Best Answer

    Você pode usar apenas COALESCEo valor original. Se o valor JSONB não existir, você obtém nulo, que é substituído pelo original, o que significa que a operação é praticamente nula.

    create or replace function on_book_modifications () 
returns trigger 
language plpgsql 
as $$
begin

-- Here (new_data only contains the 'name' field)
UPDATE books
SET
  name = COALESCE(new.new_data ->> 'name', books.name),
  nb_pages = COALESCE((new.new_data ->> 'nb_pages')::int, books.nb_pages)
WHERE books.id = new.book_id;

return new;

end;
$$

    db<>violino

    • 1

relate perguntas