`jsonb_set` в PostgreSQL: как точечно обновлять поля внутри `JSONB`

Представьте обычную ситуацию: у пользователя сменился город. Профиль хранится в колонке JSONB, внутри — имя, язык, настройки уведомлений, адрес, часовой пояс и ещё десяток полей.

Можно сделать так:

1. Прочитать весь JSON из базы.
2. Передать его в приложение.
3. Изменить одно поле.
4. Записать весь JSON обратно.

Но это неудобно и опасно. Пока приложение читало старый документ и готовило новую версию, кто-то мог обновить соседнее поле. Например, один запрос поменял город, а другой — настройки уведомлений. Если потом записать старую копию JSON целиком, можно случайно затереть чужое изменение.

Для таких задач в PostgreSQL есть функция jsonb_set. Она позволяет обновить одно поле внутри JSONB прямо в базе.

Главная идея простая:

jsonb_set возвращает новую копию JSON-документа, где значение по указанному пути заменено, а всё остальное осталось как было.

То есть функция не «лезет в приложение», не заставляет вручную пересобирать весь документ и не трогает соседние ключи по смыслу вашего запроса.

Зачем нужен jsonb_set

Допустим, в таблице users есть колонка profile:

{
  "name": "Alex",
  "address": {
    "city": "Quito",
    "zip": "170150"
  },
  "settings": {
    "theme": "dark",
    "lang": "en"
  }
}

Нужно поменять только город: вместо Quito записать Lima.

Для этого не нужно переписывать весь JSON руками. Можно указать путь до нужного поля и новое значение:

SELECT jsonb_set(
  '{"name": "Alex", "address": {"city": "Quito", "zip": "170150"}}'::jsonb,
  '{address,city}',
  '"Lima"'
);

Результат:

{
  "name": "Alex",
  "address": {
    "city": "Lima",
    "zip": "170150"
  }
}

Поле city изменилось, а name и zip остались на месте.

Синтаксис jsonb_set

Функция выглядит так:

jsonb_set(target, path, new_value, create_missing)

У неё четыре аргумента:

Аргумент	Что означает
target	исходный JSON-документ типа jsonb
path	путь к месту, которое нужно заменить
new_value	новое значение в формате JSON
create_missing	создавать ли отсутствующий последний ключ

Последний аргумент необязательный. Если его не указать, PostgreSQL считает, что он равен true.

Чаще всего вы будете видеть такую форму:

jsonb_set(profile, '{address,city}', '"Lima"')

Читается так:

Возьми JSON из profile, зайди в address, внутри него найди city и замени значение на "Lima".

Как записывается путь

Путь в jsonb_set — это текстовый массив.

Например:

'{address,city}'

Это значит:

1. Найди ключ address.
2. Внутри него найди ключ city.

Если JSON глубже, путь просто становится длиннее:

'{settings,notifications,email}'

Такой путь означает:

1. settings
2. потом notifications
3. потом email

Пример:

SELECT jsonb_set(
  '{"settings": {"notifications": {"email": false, "sms": true}}}'::jsonb,
  '{settings,notifications,email}',
  'true'
);

Результат:

{
  "settings": {
    "notifications": {
      "email": true,
      "sms": true
    }
  }
}

Обратите внимание: sms не исчез. Мы поменяли только email.

Новое значение должно быть валидным JSON

Одна из самых частых ошибок новичков — забыть, что третий аргумент jsonb_set должен быть JSON-значением.

Строку нужно передавать с JSON-кавычками:

SELECT jsonb_set(
  '{"city": "Quito"}'::jsonb,
  '{city}',
  '"Lima"'
);

А вот так нельзя:

SELECT jsonb_set(
  '{"city": "Quito"}'::jsonb,
  '{city}',
  'Lima'
);

Lima без кавычек — это невалидный JSON.

С числами наоборот: если нужно записать число, кавычки внутри JSON не нужны.

SELECT jsonb_set(
  '{"score": 7}'::jsonb,
  '{score}',
  '10'
);

Результат:

{
  "score": 10
}

А если написать так:

SELECT jsonb_set(
  '{"score": 7}'::jsonb,
  '{score}',
  '"10"'
);

Результат будет другим:

{
  "score": "10"
}

В первом случае 10 — число. Во втором случае "10" — строка. Для человека разница выглядит маленькой, но для базы это разные типы. Потом числовые сравнения, сортировки и расчёты могут начать вести себя не так, как вы ждёте.

Как обновить поле в таблице через UPDATE

Сам по себе jsonb_set ничего не меняет в таблице. Он просто возвращает новый JSON.

Чтобы сохранить результат, нужно присвоить его обратно в колонку.

Например, поменяем город пользователя с id = 42:

UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"Lima"')
WHERE id = 42;

Здесь происходит вот что:

1. PostgreSQL берёт текущее значение profile.
2. Создаёт новую версию JSON с изменённым городом.
3. Записывает эту новую версию обратно в profile.

Именно поэтому слева стоит:

SET profile =

Без присваивания функция просто посчитала бы новое значение, но в таблице ничего бы не изменилось.

Как обновить несколько полей сразу

Если нужно поменять несколько полей, вызовы jsonb_set можно вкладывать друг в друга.

Например, поменяем город и одновременно отметим профиль как проверенный:

UPDATE users
SET profile = jsonb_set(
  jsonb_set(profile, '{address,city}', '"Lima"'),
  '{verified}',
  'true'
)
WHERE id = 42;

Внутренний jsonb_set меняет город:

jsonb_set(profile, '{address,city}', '"Lima"')

Внешний jsonb_set берёт уже обновлённый JSON и добавляет или меняет поле verified:

jsonb_set(..., '{verified}', 'true')

Такой запрос можно читать изнутри наружу: сначала одно изменение, потом второе.

Как обновлять значения из других колонок

В реальных задачах новое значение часто не пишут руками. Его берут из другой колонки или вычисляют прямо в запросе.

Допустим, в таблице orders есть:

• amount — сумма заказа;
• meta — JSONB с дополнительными данными.

Нужно записать скидку в JSON: 10% от суммы заказа.

UPDATE orders
SET meta = jsonb_set(meta, '{discount}', to_jsonb(amount * 0.1))
WHERE status = 'paid';

Функция to_jsonb превращает обычное SQL-значение в JSONB-значение.

Это удобнее и безопаснее, чем вручную собирать строку. Особенно когда значение не фиксированное, а вычисляется на лету.

Для текстового значения это тоже полезно. Например, если город хранится в отдельной колонке new_city:

UPDATE users
SET profile = jsonb_set(profile, '{address,city}', to_jsonb(new_city))
WHERE new_city IS NOT NULL;

PostgreSQL сам превратит текст из new_city в корректную JSON-строку.

Как работает create_missing

Четвёртый аргумент jsonb_set называется create_missing.

Он отвечает за вопрос:

Что делать, если последнего ключа в пути ещё нет?

По умолчанию значение true. Значит, если последний ключ отсутствует, PostgreSQL создаст его.

Пример:

SELECT jsonb_set(
  '{"city": "Lima"}'::jsonb,
  '{verified}',
  'true',
  true
);

Результат:

{
  "city": "Lima",
  "verified": true
}

Ключа verified не было, но он появился.

Если поставить false, PostgreSQL будет строже: обновлять можно только существующий ключ.

SELECT jsonb_set(
  '{"city": "Lima"}'::jsonb,
  '{verified}',
  'true',
  false
);

Результат:

{
  "city": "Lima"
}

Документ не изменился, потому что ключа verified не было.

Это полезно, когда вы хотите защититься от случайных опечаток.

Например, хотели обновить verified, но случайно написали verifed. При create_missing = true PostgreSQL спокойно создаст неправильный ключ. При create_missing = false он не станет добавлять новое поле.

Важная ловушка: create_missing создаёт только последний ключ

create_missing не строит весь путь с нуля.

Он может создать только последний элемент пути, если все промежуточные части уже существуют.

Допустим, есть JSON:

{
  "name": "Alex"
}

Попробуем записать город по пути address.city:

SELECT jsonb_set(
  '{"name": "Alex"}'::jsonb,
  '{address,city}',
  '"Lima"',
  true
);

Можно ожидать, что PostgreSQL создаст объект address, а внутри него city.

Но так не произойдёт.

Результат останется прежним:

{
  "name": "Alex"
}

Почему? Потому что промежуточного объекта address нет. PostgreSQL не знает, какую структуру нужно построить на этом месте, поэтому возвращает документ без изменений.

Это важный момент: если путь недостижим, jsonb_set обычно не падает с ошибкой, а просто возвращает исходный документ.

Для новичка это особенно неприятно: запрос выполнился, ошибки нет, а данные не изменились.

Как создать вложенный объект, если его нет

Если нужно создать недостающую вложенную структуру, обычно делают это в несколько шагов.

Например, сначала гарантируют наличие объекта address, а потом уже записывают в него city.

UPDATE users
SET profile = jsonb_set(
  jsonb_set(profile, '{address}', coalesce(profile -> 'address', '{}'::jsonb)),
  '{address,city}',
  '"Lima"'
)
WHERE id = 42;

Здесь идея такая:

1. Внутренний jsonb_set создаёт address, если его нет.
2. Внешний jsonb_set записывает city внутрь address.

Выражение:

coalesce(profile -> 'address', '{}'::jsonb)

означает:

Возьми текущий address, а если его нет, используй пустой JSON-объект.

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

Как обновлять элементы массива

Путь может вести не только к полям объекта, но и к элементам массива.

Допустим, есть JSON:

{
  "tags": ["new", "vip", "eu"]
}

Чтобы заменить первый тег, используем индекс 0:

SELECT jsonb_set(
  '{"tags": ["new", "vip", "eu"]}'::jsonb,
  '{tags,0}',
  '"fresh"'
);

Результат:

{
  "tags": ["fresh", "vip", "eu"]
}

Индексация начинается с нуля:

• 0 — первый элемент;
• 1 — второй;
• 2 — третий.

Можно использовать и отрицательные индексы. Индекс -1 означает последний элемент:

SELECT jsonb_set(
  '{"tags": ["new", "vip", "eu"]}'::jsonb,
  '{tags,-1}',
  '"latam"'
);

Результат:

{
  "tags": ["new", "vip", "latam"]
}

Это удобно, когда нужно поменять последний элемент массива и не хочется заранее считать длину.

jsonb_set не удаляет ключи

Важно не путать две разные операции:

• заменить значение на null;
• удалить ключ совсем.

Если сделать так:

SELECT jsonb_set(
  '{"city": "Lima", "zip": "170150"}'::jsonb,
  '{zip}',
  'null'
);

Результат будет таким:

{
  "city": "Lima",
  "zip": null
}

Ключ zip остался. Просто его значение стало null.

Если нужно именно удалить ключ, нужен не jsonb_set, а оператор -.

Как удалить ключ верхнего уровня

Оператор - удаляет ключ из JSONB-объекта.

SELECT '{"city": "Lima", "tmp": 1}'::jsonb - 'tmp';

Результат:

{
  "city": "Lima"
}

Ключ tmp исчез полностью.

В таблице это выглядит так:

UPDATE users
SET profile = profile - 'tmp'
WHERE id = 42;

Как удалить несколько ключей сразу

Можно удалить несколько ключей верхнего уровня, передав массив:

SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{a,b}'::text[];

Результат:

{
  "c": 3
}

Это удобно для очистки временных или устаревших полей.

Например:

UPDATE users
SET profile = profile - '{tmp,debug,old_flag}'::text[]
WHERE profile ? 'tmp';

Здесь удаляются ключи tmp, debug и old_flag.

Как удалить элемент массива

Оператор - умеет удалять элемент массива по индексу.

SELECT '["a", "b", "c"]'::jsonb - 1;

Результат:

["a", "c"]

Индекс 1 — это второй элемент, поэтому из массива исчезло значение b.

Как удалить вложенный ключ через #-

Если ключ лежит глубоко внутри JSON, используют оператор #-.

Ему передают путь, похожий на путь в jsonb_set.

Допустим, есть профиль:

{
  "address": {
    "city": "Lima",
    "zip": "170150"
  }
}

Удалим вложенный ключ zip:

UPDATE users
SET profile = profile #- '{address,zip}'
WHERE id = 42;

Результат внутри profile будет таким:

{
  "address": {
    "city": "Lima"
  }
}

То есть:

• - подходит для ключей верхнего уровня и элементов массива;
• #- подходит для удаления по вложенному пути.

Практический пример: обновляем настройки пользователя

Допустим, в таблице users профиль хранится так:

{
  "name": "Alex",
  "settings": {
    "theme": "light",
    "notifications": {
      "email": false,
      "sms": true
    }
  }
}

Нужно:

1. Поменять тему на dark.
2. Включить email-уведомления.
3. Удалить старый ключ sms.

Запрос может быть таким:

UPDATE users
SET profile = (
  jsonb_set(
    jsonb_set(profile, '{settings,theme}', '"dark"'),
    '{settings,notifications,email}',
    'true'
  ) #- '{settings,notifications,sms}'
)
WHERE id = 42;

Что здесь происходит:

1. Первый jsonb_set меняет theme.
2. Второй jsonb_set меняет email.
3. Оператор #- удаляет вложенный ключ sms.

Да, выражение получилось плотным. Но оно делает точечное изменение прямо в базе и не требует вытаскивать весь JSON в приложение.

Чем это отличается от обычного обновления столбца

Важно понимать: PostgreSQL всё равно обновляет строку в таблице. Он не меняет один байт внутри JSON «на месте» как в текстовом редакторе.

Но с точки зрения SQL-запроса вы описываете точечное изменение:

SET profile = jsonb_set(profile, '{address,city}', '"Lima"')

Это лучше, чем собирать весь JSON заново вручную:

SET profile = '{"name": "Alex", "address": {"city": "Lima"}}'::jsonb

Во втором варианте легко потерять поля, которые вы забыли дописать. В первом варианте меняется только нужный путь, а остальная структура сохраняется.

Как это выглядит в других СУБД

В PostgreSQL для точечных обновлений есть jsonb_set, а для удаления — операторы - и #-.

В других базах синтаксис отличается.

В MySQL похожая задача решается через JSON_SET:

UPDATE users
SET profile = JSON_SET(profile, '$.address.city', 'Lima')
WHERE id = 42;

Для удаления используют JSON_REMOVE:

UPDATE users
SET profile = JSON_REMOVE(profile, '$.address.zip')
WHERE id = 42;

Если нужно обновлять только существующие значения и не создавать новые, в MySQL есть JSON_REPLACE.

В ClickHouse подход другой: JSON-функции чаще используют для чтения и извлечения значений. Для частичных правок JSON-документов ClickHouse обычно не так удобен, и на практике значение чаще пересобирают или перезаписывают целиком.

Идея везде одна: JSON можно менять по пути. Но конкретный синтаксис у каждой базы свой.

Частые ошибки

Забыть присвоить результат обратно

Неправильно думать, что jsonb_set сам меняет таблицу.

SELECT jsonb_set(profile, '{address,city}', '"Lima"')
FROM users
WHERE id = 42;

Этот запрос только покажет новую версию JSON. В таблице ничего не изменится.

Для настоящего обновления нужен UPDATE:

UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"Lima"')
WHERE id = 42;

Передать строку не как JSON

Неправильно:

SELECT jsonb_set(
  '{"city": "Quito"}'::jsonb,
  '{city}',
  'Lima'
);

Правильно:

SELECT jsonb_set(
  '{"city": "Quito"}'::jsonb,
  '{city}',
  '"Lima"'
);

Строковое JSON-значение должно быть в двойных кавычках внутри SQL-строки.

Случайно записать число строкой

Число:

SELECT jsonb_set('{"score": 7}'::jsonb, '{score}', '10');

Строка:

SELECT jsonb_set('{"score": 7}'::jsonb, '{score}', '"10"');

Для JSON это разные значения.

Ожидать, что create_missing создаст весь путь

Так не работает:

SELECT jsonb_set(
  '{"name": "Alex"}'::jsonb,
  '{address,city}',
  '"Lima"',
  true
);

Если address не существует, PostgreSQL не создаст его автоматически вместе с city. Промежуточные части пути должны уже существовать.

Заменить значение на null и думать, что ключ удалён

Так ключ остаётся:

SELECT jsonb_set('{"zip": "170150"}'::jsonb, '{zip}', 'null');

Результат:

{
  "zip": null
}

Чтобы удалить ключ, используйте - или #-.

Главное

jsonb_set — основной инструмент PostgreSQL для точечного обновления данных внутри JSONB.

Запомните несколько правил:

• jsonb_set возвращает новую версию JSON, а не меняет документ сам по себе;
• в UPDATE результат нужно присвоить обратно в колонку;
• путь записывается как текстовый массив, например '{address,city}';
• новое значение должно быть валидным JSON;
• строки пишутся как '"Lima"', числа — как '10', булевы значения — как 'true';
• create_missing создаёт только последний ключ, но не строит весь вложенный путь;
• для удаления ключей используйте - и #-, а не замену на null;
• для динамических значений удобно использовать to_jsonb.

Если совсем коротко по смыслу: jsonb_set — это аккуратная точечная правка JSON внутри PostgreSQL. Вы показываете базе путь, даёте новое значение, присваиваете результат обратно в колонку — и получаете обновлённый документ без ручной пересборки всего профиля или заказа.