Dzień dobry
Mam taką strukturę.
CREATE TABLE public.unit
{
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
sn text not null,
name text not null,
(...)
last_data jsonb
}
last_data
{
"temperature" : 0
(...)
"sensors":
{
{"no": 1, value:100},
{"no": 2, value:421},
{"no": 3, value:421},
(...)
{"no": 20, value:123},
}
}
W last_data są przechowywane ostatnio wysłane dane. Każde z pól w last_data jest osobno aktualizowane. Wynika to z tego, że te informacje dostaje w losowej kolejności.
Normalnie tworząc tabele public.unit_last_data z kolumnami key i value mam załatwiony temat (robię 1 merge(update/insert w zależności od tego czy wiersz istnieje)) ale chciałbym spróbówac czegoś innego.
Chciałbym przykładowo sam czujnik nr 1 zaktualizować ale mam pewne wątpliwości:
last_datamoże byćnull.last_datamoże nie byćnullale "sensors" może byćnull.last_datamoże nie mieć czunika w "sensors" (podobnie do insert)last_datamoze już mieć czujnik w "sensors" (podobnie do update)
Jak to robią profesjonaliści?
Gdybym uprościł strukturę tak:
{
"temperature" : 0
(...)
"sensor1": -1,
"sensor2": 421,
"sensor3": 421,
(...)
"sensor20": 123
}
To byłoby troche łatwiej (znika punkt 2 i 3). Realnie to będzie może być tak z 1 poziom głębkości więcej.
Alternatywne rozwiązanie to wczytuje cała tablicę "sensors" do programu, zmieniam co chce i zapisuje.
Tylko czy wtedy nie stracę danych jak 2 rózne czujniki będa aktualizowane tym samym czasie??
Nie jestem przekonany co do takich odpowiedzi od AI.
UPDATE public.unit
SET last_data = CASE
WHEN last_data IS NULL THEN
jsonb_build_object(
'temperature', 0,
'sensors', jsonb_build_array(jsonb_build_object('no', 1, 'value', 100))
)
ELSE
CASE
WHEN EXISTS (
SELECT 1 FROM jsonb_array_elements(last_data->'sensors') elem
WHERE (elem->>'no')::int = '1'
)
THEN
jsonb_set(
last_data,
'{sensors}',
(
SELECT jsonb_agg(
CASE
WHEN (elem->>'no')::int = '1' THEN
jsonb_build_object('no', 1, 'value', 100)
ELSE elem
END
)
FROM jsonb_array_elements(last_data->'sensors') elem
)
)
ELSE
jsonb_set(
last_data,
'{sensors}',
jsonb_set(
last_data->'sensors',
jsonb_array_length(last_data->'sensors'),
jsonb_build_object('no', 1, 'value', 100)
)
)
END
END
WHERE id=101;
SQL 
PostgreSQL