Инструкция о выборе/изменении данных, хранящихся в JSONB формате.
Операторы, доступные для использования
Операнд | Тип правого операнда | Описание |
-> | int | Получить элемент массива JSON по индексу (индексация с нуля, отрицательные целые — индексация с конца) |
-> | text | Получить значение по ключу |
->> | int | Получить значение по индексу в виде текста |
->> | text | Получить значение по ключу в виде текста |
#> | text[] | Получить значение по указанному пути |
##> | text[] | Получить значение по указанному пути в виде текста |
Функции обновляющие данные
jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])
Возвращаемое значение: jsonb
Возвращает объект target, в котором элемент, на который указывает путь path, заменяется значением new_value либо значение new_value добавляется, когда параметр create_if_missing равен true (это значение по умолчанию) и элемент, на который указывает path, не существует. Чтобы это изменение произошло, все предыдущие элементы, на которые указывает путь, должны существовать. В противном случае target возвращается без изменений. Как и с операторами, принимающими пути, отрицательные целые числа, фигурирующие в path, отсчитывают элементы с конца JSON-массива. Если на последнем шаге пути указывается индекс, выходящий за границы массива, и параметр create_if_missing равен true, новое значение добавляется в начало массива, когда индекс отрицательный, или в конец, когда он положительный.
jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text]]
Возвращаемое значение: jsonb
Если значение new_value отлично от NULL, эта функция действует так же, как и jsonb_set. В противном случае она действует согласно значению null_value_treatment, которое может принимать значения:
- ‘raise_exception’ — выбрасывать исключения, если new_value null’
- ‘use_json_null’ (значение по умолчанию) — если new_value null, будет использоваться JSON null
- ‘delete_key’ — если new_value null ключ будет удален
- ‘return_target’ — если new_value null функция вернет значение указанное в пути
Пример обновления данных
Тестовые данные (таблица person, столбец personb (JSONB)):
{ "first_name": "Kristi", "last_name": "Moon", "langs": [ "JavaScript", "php" ] }
Запрос на обновление данных по ключу (указано значение false как значение create_missing чтобы не создавалось значение если отсутствует):
UPDATE public.person SET personb = jsonb_set(personb, '{first_name}', 'KRI', false) WHERE personb ->> 'last_name' = 'Moon';
Запрос на добавление данных:
UPDATE public.person SET personb = jsonb_set(personb, '{langs, 2}', 'html', true) WHERE personb ->> 'last_name' = 'Moon';
Что делать если значения хранятся в «неопределенном» формате?
Если в запросе необходимо выбрать поле, в котором могут храниться как текстовые, так и числовые значения, то в запросе потребуется приведение данных
Например, если стоит задача заменить значение color поля properties таблицы objectsProperties для всех строк, у которых color равен 1.
id | key | properties |
1 | first | {«text»: «some text value», color: «1»} |
2 | second | {«text»: «loream text here…», color: 1} |
3 | third | {«text»: «the game…», color: 2} |
4 | fourth | {«size»: 12} |
То необходимо использовать запрос такого вида:
SELECT * FROM objectsProperties WHERE properties—>’color’::text=’1′
Как произвести поиск по дочерним данным, путь к которым не известен
Пусть столбец data таблицы table содержит данные в виде:
{ "001": { "a": "000A", "b": "000B", "c": "000C" }, "002": { "b": "000X", "d": "000R" } }
Требуется выбрать все строки, такие что в столбце data в каком-то из объектов находится b = “000B”. Запрос на получение данных будет:
SELECT * FROM table AS t WHERE jsonb_path_exists(t.data, '$.*.b ? (@ == "000B")');