postgresql jsonb array(jsonb list) 제어

postgresql jsonb array타입(jsonb list)에 대해 json 추가/수정/삭제

 

Insert

INSERT INTO table_name (column1, column2, column_jsonb_list)
VALUES ('value1', 'value2', '["jsonb_value1", "jsonb_value2", ... ]'::jsonb[])

 

Update

UPDATE table_name
SET column_name = array_to_json(
  array_agg(
    CASE
      WHEN element->>'id' = 'uuid_value' THEN
        jsonb_set(element, '{deactive_dt}', to_jsonb(current_date))
      ELSE element
    END
  )
)
FROM (
  SELECT jsonb_array_elements(column_name) AS element
  FROM table_name
) sub;

Delete

SELECT jsonb_set('{"data": [1, 2, 3, 4, 5]}'::jsonb, '{data}', '[]');