Jak poprawnie przechowywać dane w jsonb?

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:

1. last_data może być null.
2. last_data może nie być null ale "sensors" może być null.
3. last_data może nie mieć czunika w "sensors" (podobnie do insert)
4. last_data moze 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;

Nie wiem czy nie za bardzo pociołes tego JSON-a, ale taki:

{
    "temperature" : 0
    "sensors":
    {
        {"no": 1, "value":100},
        {"no": 2, "value":421},
        {"no": 3, "value":421},
        {"no": 20, "value":123}
    }
}

Jest nieprawidłowy, więc cięzko go aktualizować. a nawet dodać do tabeli, jeżeli weźmiemy poprawnego JSON-a:

{
  "temperature": 0,
  "sensors": [
    {
      "no": 1,
      "value": 100
    },
    {
      "no": 2,
      "value": 421
    },
    {
      "no": 3,
      "value": 421
    },
    {
      "no": 20,
      "value": 123
    }
  ]
}

to można tak:

UPDATE tb
SET j = CASE
    WHEN j @> '{"sensors": [{"no": 5}]}'::jsonb THEN
        jsonb_set(j, '{sensors}', (j->'sensors') - 'value' || '{"no": 5, "value": 1}'::jsonb)
    ELSE
        jsonb_set(j, '{sensors}', COALESCE(j->'sensors', '[]'::jsonb) || '[{"no": 5, "value": 1}]'::jsonb)
END;

http://sqlfiddle.com/#!17/299460/11

Jaką drogę nie obierzesz zacznij od poprawnego JSON-a