Исправление ключей JSON в PostgreSQL

Я хочу убедиться или подтвердить, что столбец service_config содержит все ключи, упомянутые выше, ни больше, ни меньше. Однако для ключей может быть нулевое значение.

Превратите их в столбцы.

JSON удобен, когда вам нужно просто вывести некоторые данные в строку, и вы не уверены, что это будет. Теперь, когда вы уверены, что это будет, и хотите больше ограничений, это то, что столбцы делают лучше всего.

alter table whatever add column con_type text;
alter table whatever add column capacity integer;
alter table whatever add column capacity_unit text;

update whatever set
  con_type = data->'con_type',
  capacity = data->'capacity',
  capacity_unit = data->'capacity_unit';

alter table whatever drop column data

Колонки всегда будут. Их значения могут быть нулевыми. Вы можете добавить check ограничений и индексов для каждого столбца. Никаких дополнительных подтверждений не требуется.

Если вам все еще нужен json, используйте jsonb_build_object.

select
  jsonb_build_object(
    'con_type', con_type,
    'capacity', capacity,
    'capacity_unit', capacity_unit
  )
from whatever;

create view whatever_as_json
select
  *,
  jsonb_build_object(
    'con_type', con_type,
    'capacity', capacity,
    'capacity_unit', capacity_unit
  ) as data
from whatever;

Обратите внимание, что я использую text, а не char, потому что в Postgres нет никаких преимуществ перед char. См. совет в 8.3. Типы символов

Между этими тремя типами нет разницы в производительности, за исключением увеличения места для хранения при использовании типа с заполнением пробелами и нескольких дополнительных циклов ЦП для проверки длины при сохранении в столбец с ограничениями по длине. В то время как в некоторых других системах баз данных у character(n) есть преимущество в производительности, в PostgreSQL такого преимущества нет; на самом деле character(n) обычно является самым медленным из трех из-за дополнительных затрат на хранение. В большинстве случаев вместо этого следует использовать текст или символы.