postgresql

• where для поля типа JSONB
• Функция удаления полей в данных JSONB

where для поля типа JSONB

Для получения записей по условию значения в поле JSONB нужно выполнить следующую конструкцию, например:

SELECT * FROM dev8.widgets
WHERE body->>'namespace' = 'ext_aca2d909-8a70-406f-9fc0-a5370fec83a1'
ORDER BY id ASC 

где,
body - поле типа JSONB
namеspace - элемент в структуре JSON

Функция удаления полей в данных JSONB

Если уже есть заполненные данные в body какого то Приложения и была правка метаданных в базе с удалением полей, при обновлении элемента приложения система будет ругаться. Чтобы почистить данные в body, необходимо в схеме создать функцию:

-- head - название схеме (площадки ELMA365)
CREATE OR REPLACE FUNCTION head.json_object_delete_keys("json" json, VARIADIC "keys_to_delete" TEXT[])
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
   FROM json_each("json")
   WHERE "key" <> ALL ("keys_to_delete")),
  '{}'
)::json
$function$;

После этого выполнить запрос по очистке данных:

-- где 'type', 'signatory_position', 'base_action', 'set_bank_details' - поля к удалению внутри body
update head."_clients:_companies"
set body = head.json_object_delete_keys(body::json, 'type', 'signatory_position', 'base_action', 'set_bank_details');