Как извлечь данные из JSON?

Question

[un1t]

В таблице есть jsonb поле, назовем его "data"

Пример того что там находится

{
	"items": [
		{"name": "A", "category_id": 1},
		{"name": "B", "category_id": 2}
	]
}

Нужно найти все строки в таблице которых присутсвуют категории 1, 3 или 5.
Т.е. если извлечть эти категории в массив int[] то можно воспользоваться оператором &&

SELECT * FROM table WHERE some_magic_extraction(data) && array[1,3,5];

Answer 1

[0xD34F]

SELECT *
FROM table
WHERE (SELECT array_agg((t->>'category_id')::INT) FROM jsonb_array_elements(data->'items') AS t) && ARRAY[1,3,5]

Или так:

SELECT *
FROM table
WHERE ARRAY(SELECT t.category_id FROM jsonb_to_recordset(data->'items') AS t(category_id INT)) && ARRAY[1,3,5]

Answer 2

[Melkij]

Если не нужен join со значениями, а только фильтр - то

where array(select (j->>'category_id')::int from jsonb_array_elements(data->'items') j) && array[1,3,5];

Можно загнать подзапрос с построением массива в immutable хранимку и повесить по ней gin или gist индекс.

Answer 3

[x67]

SELECT jsv.value#>'{0,name}' as "name" ,jsv.value#>'{0,category_id}' as cat_id
FROM table, jsonb_each(jsonfield) as jsv
WHERE 
whoop_whoop_whoop='abc' 
and jsv.value#>'{0,name}'='B'

магическое извлечение происходит в функции jsonb_each(поле jsonb)
магическая проверка происходит с объектом jsv в теле WHERE.
Для полного понимания как это работает, читайте мануалы постгрес. Они отлично документированы!
Там же вы узнаете, почему обращение идет к jsv.value и что будет если вывести jsv.key, а также какой оператор удобнее использовать вместо #> в вашем случае

Comments

[un1t]

Спасибо за предложенный вариант. Но чето-то как-то капец сложно получилось.