фильтровать данные на jsonb с помощью postgres

представьте, что у вас есть веб-сайт, на котором люди размещают свою рекламу. Итак, каждое объявление имеет некоторые выбранные свойства, например, автомобили имеют разные типы двигателей, шестерни, цвета и т. Д. Эти свойства пользователь выбирает перед отправкой объявления. Я сохраняю выбранные свойства в формате jsonb в таблице списков, смотрю столбец данных:

ссылка на изображение.

Итак, каждый листинг содержит такие данные:

{
   "properties":[
      {
         "id":"1",
         "value_id":"1"
      },
      {
         "id":"2",
         "value_id":"5"
      },
      {
         "id":"3",
         "value_id":"9"
      },
      {
         "id":"4",
         "value":"2.0"
      },
      {
         "id":"7",
         "value":"2017"
      },
      {
         "id":"6",
         "value":"180.000"
      }
   ]
}

Теперь вопрос:

1) Как фильтровать списки по тем идентификаторам и значениям, которые находятся в json? Например, покажите списки, где id = 2, а его значение = 5 И id = 3, а его значение = 9 и так далее. Мне не нужно ИЛИ, мне нужно И. Итак, фильтруйте данные по нескольким идентификаторам и значениям.

2) Первая точка + возможность сравнивать идентификаторы и значения (больше или меньше).


postgresql sql jsonb
person Artur Nazarov    schedule 13.01.2018    source источник
comment
Из примера похоже, что вы храните в jsonb то, что должно быть отдельной таблицей.   -  person Justinas Marozas    schedule 14.01.2018
comment
@Justinas Marozas, в отдельной таблице у меня есть переводы и другие метаданные для этих свойств. Это потому, что сайт имеет несколько языков. И я присоединяюсь к этим значениям и идентификаторам другой таблицы.   -  person Artur Nazarov    schedule 14.01.2018
comment
Но почему вы используете jsonb для этих данных с регулярной структурой? Почему бы не создать отдельную таблицу из трех столбцов (в комплекте с FK)? Или даже массив jsonb типа array['{"id":"1","value_id":"1"}'::jsonb, '{"id":"2","value_id":"5"}', ...]?   -  person mu is too short    schedule 14.01.2018

Ответы (1)


arrow_upward
0
arrow_downward

отвечая на первый пункт, я, наверное, впервые нахожу применение jsonb[]:

t=# with c(a,j) as (values(18,'{
   "properties":[
      {
         "id":"1",
         "value_id":"1"
      },
      {
         "id":"2",
         "value_id":"5"
      },
      {
         "id":"3",
         "value_id":"9"
      },
      {
         "id":"4",
         "value":"2.0"
      },
      {
         "id":"7",
         "value":"2017"
      },
      {
         "id":"6",
         "value":"180.000"
      }
   ]
}'::jsonb), (19,'{"properties":[{"id": "1", "value_id": "1"}]}'))
, m as (select a, array_agg(jb.value)::jsonb[] ar from c, jsonb_array_elements(j->'properties') jb group by a)
select a 
from m 
where '{"id": "1", "value_id": "1"}'::jsonb = any(ar) 
  and '{"id": "3", "value_id": "9"}'::jsonb = any(ar);
 a
----
 18
(1 row)

а для второго требования - это будет не так коротко, так как вам нужно сравнить (и, таким образом, разобрать json):

t=# with c(a,j) as (values(18,'{
   "properties":[
      {
         "id":"1",
         "value_id":"1"
      },
      {
         "id":"2",
         "value_id":"5"
      },
      {
         "id":"3",
         "value_id":"9"
      },
      {
         "id":"4",
         "value":"2.0"
      },
      {
         "id":"7",
         "value":"2017"
      },
      {
         "id":"6",
         "value":"180.000"
      }
   ]
}'::jsonb), (19,'{"properties":[{"id": "1", "value_id": "1"}]}'))
, m as (select a, jb.value->>'id' id,jb.value->>'value_id' value_id from c, jsonb_array_elements(j->'properties') jb)
, n as (select m.*, count(1) over (partition by m.a)
from m
join c on c.a = m.a and ((id::int >= 1 and value_id::int <2) or (id::int >2 and value_id::int <= 9)))
select distinct a from n
where count > 1;
 a
----
 18
(1 row)

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

person Vao Tsun    schedule 15.01.2018
comment
Да, это действительно не короткий вопрос. После своего исследования я думаю, что соглашусь с другими комментариями выше, лучше создать отдельную таблицу для значений и ключей и все. Проще - лучше. - person Artur Nazarov; 16.01.2018
comment
очевидно, создание таблицы вместо хранения ее данных в jsonb - лучшая идея :) Я отвечал на ваш вопрос - как вы можете ЗАПРОСИТЬ его, а не решить проблему нормализации - person Vao Tsun; 16.01.2018