Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE legacy_hash (
  _key text PRIMARY KEY,
  data jsonb
);

INSERT INTO legacy_hash (_key, data) VALUES
('x', '{"f":10}');

CREATE TABLE



INSERT 0 1


select
jsonb_path_query_first(data, '$.keyvalue()[0]')->>'key'
from legacy_hash


?column?


f



SELECT 1


WITH updates AS (
    SELECT UNNEST('{"x"}'::TEXT[]) AS _key,
           UNNEST('{"f"}'::TEXT[]) AS field,
           UNNEST('{1}'::NUMERIC[]) AS value
)
INSERT INTO legacy_hash (_key, data)
SELECT _key, jsonb_build_object(field, value)
FROM updates
ON CONFLICT (_key)
DO UPDATE SET data = jsonb_set(
    legacy_hash.data, 
    ARRAY[jsonb_path_query_first(excluded.data, '$.keyvalue()[0]')->>'key'],
    to_jsonb(
      COALESCE((legacy_hash.data->>(jsonb_path_query_first(excluded.data, '$.keyvalue()[0]')->>'key'::text))::NUMERIC, 0)
      + (jsonb_path_query_first(excluded.data, '$.keyvalue()[0]')->>'value')::NUMERIC
    )
)

INSERT 0 1


SELECT
    data
FROM legacy_hash;


data


{"f": 11}



SELECT 1


WITH updates AS (
    SELECT UNNEST('{"x"}'::TEXT[]) AS _key,
           UNNEST('{"f"}'::TEXT[]) AS field,
           UNNEST('{1}'::NUMERIC[]) AS value
)
MERGE INTO legacy_hash AS lh
USING updates AS u
ON u._key = lh._key
WHEN NOT MATCHED THEN
  INSERT (_key, data)
  VALUES (u._key, jsonb_build_object(u.field, u.value))
WHEN MATCHED THEN
  UPDATE SET
    data = jsonb_set(
      lh.data,
      ARRAY[u.field],
      to_jsonb(COALESCE((lh.data->>u.field)::NUMERIC, 0) + u.value)
    )
;

MERGE 1


SELECT
    data
FROM legacy_hash;


data


{"f": 12}



SELECT 1