Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employees (
    vertical_id INT,
    name TEXT,
    employee_data JSONB
);

CREATE TABLE


INSERT INTO employees (vertical_id, name, employee_data) VALUES
(1, 'accounts', '{
    "employees": [
        {"empId":  1, "desig": "TL", "sal":  10000},
        {"empId":  2, "desig": "staff", "sal":  5000},
        {"empId":  3, "desig": "staff"}
    ]
}'),
(2, 'HR', '{
    "employees": [
        {"empId":  1, "desig": "TL", "sal":  10000},
        {"empId":  2, "desig": "staff", "sal":  5000},
        {"empId":  3, "desig": "staff"}
    ]
}');

INSERT 0 2


SELECT employee_data->>'employees' AS emp_data from employees


emp_data


[{"sal": 10000, "desig": "TL", "empId": 1}, {"sal": 5000, "desig": "staff", "empId": 2}, {"desig": "staff", "empId": 3}]

[{"sal": 10000, "desig": "TL", "empId": 1}, {"sal": 5000, "desig": "staff", "empId": 2}, {"desig": "staff", "empId": 3}]



SELECT 2


UPDATE employees
SET employee_data = jsonb_set(
    employee_data,
    '{employees}',
    (
        SELECT jsonb_agg(
            CASE
                WHEN value ->> 'desig' = 'TL' THEN jsonb_set(value, '{sal}', to_jsonb(20000)::jsonb)
                ELSE value
            END
        )
        FROM jsonb_array_elements(employee_data -> 'employees') AS t(value)
    ),
    true
)
WHERE employee_data @> '{"employees":[{"desig":"TL"}]}';

UPDATE 2


SELECT employee_data->>'employees' AS emp_data from employees


emp_data


[{"sal": 20000, "desig": "TL", "empId": 1}, {"sal": 5000, "desig": "staff", "empId": 2}, {"desig": "staff", "empId": 3}]

[{"sal": 20000, "desig": "TL", "empId": 1}, {"sal": 5000, "desig": "staff", "empId": 2}, {"desig": "staff", "empId": 3}]



SELECT 2