Шпаргалка по JSONB в PostgreSQL

Инструкция о выборе/изменении данных, хранящихся в 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")');