ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / фильтрация данных
11 сообщений из 11, страница 1 из 1
фильтрация данных
    #39583563
Arturs Nazarovs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arturs Nazarovs
Гость
Храню данные в виде Jsonb, выглядит так. Каждый авто имеет некие свойства.

Код: javascript
1.
{"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"}]}



Представьте, сайт авито с его фильтрами. Каждый авто имеет некие свойства в формате (key/value), когда-то его владелец при подаче обьявления выбрал некие опции своего авто. Вот я и хочу фильтровать автомобили по этим свойствам. Свойства храню в формате json (уже пример привел выше этого json'a).

Надо:
Сделать выборку авто по фильтрам, а фильтруются данные по id и value_id, которые в json'e выше (где id = айтишник некого атрибута, например "КПП" или "Тип двигателя"), просто я храню id этих атрибутов. Тоже самое с value_id, это просто когда-то выбранное значение данного атрибута. Например (КПП => Ручная или Тип двигателя => "бензин").


Вот пока что непонимаю как сделать фильтрацию данных. Как сделать так, чтобы при выборке он мне показал отфильтрованные авто, (где id = 1 а его value_id = 1) а так же (id = 2 а его value_id = 5).
...
Рейтинг: 0 / 0
12.01.2018, 22:03
| Ответить | Цитировать | Написать  
фильтрация данных
    #39583603
Arturs Nazarovs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arturs Nazarovs
Гость
Пока что создал такое решение:

Код: javascript
1.
SELECT * FROM listings WHERE data->'properties' @> '[{"id": "1", "value_id":"1"}]' AND data->'properties' @> '[{"id": "2", "value_id":"5"}]'



Теперь как сделать выборку, при условии что value_id >= 5 (больше или равно)
...
Рейтинг: 0 / 0
13.01.2018, 01:49
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584044
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
Код: plaintext
{"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"}]}

Совет, у Вас в данных уже "бардак":

Код: plaintext
{"id":"3"," value_id ":"9"},{"id":"4", "value" :"2.0"}

json/jsonb --> это для динамических данных, это когда не знаешь какой параметр пользователь может придумать, да и то эти данные обычно проверяют на схему данных, тоесть определенный порядок/структура все равно есть.

Определитесь --> у Вас пользователь сам создает параметры - имена параметров!, тогда да как вариант jsonb, или всё таки выбирает из заранее подготовленного списка? тогда попробуйте нормализовать БД по параметру. Запись json по хорошему должна быть атомарна исходя из вашей задачи, иначе вероятно ошибка проектирования.
Ибо у Вас массив элементов, и выбрать Вы хотите элемент массива, а не запись, это плохо, на текущем развитии PostgreSQL ошибка.

p.s. PostgreSQL это ещё пока не графовая БД, собирать объекты нужно использовав "реляционную" модель.

Так, делать не правильно, огребете по полной при обновлениях и синхронизациях.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
-- typical error of design when using jsonb
with el_array_bad_jsonb as
 	(select 
        elem, pos- 1 as elem_index
    from 
        t, 
        jsonb_array_elements(bad_jsonb->'properties') with ordinality arr(elem, pos)
    where
        elem @> '{"value_id": "5"}' = TRUE AND elem @> '{"id": "2"}' = TRUE
    ) 
select * from el_array_bad_jsonb
;



-- output

Код: plaintext
1.
2.
elem                         |elem_index 
-----------------------------|-----------
{"id": "2", "value_id": "5"} |1



--- test jsonb

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
 {
  "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"
    }
  ]
}

--DDL
Код: plsql
1.
2.
3.
4.
create table t (
	id serial,
	bad_jsonb jsonb
);



-- init

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
INSERT INTO t 
	(bad_jsonb)
VALUES 
('	
{
  "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"
    }
  ]
}')
;
...
Рейтинг: 0 / 0
14.01.2018, 22:31
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584084
Arturs Nazarovs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arturs Nazarovs
Гость
Спасибо за ответ! Дело в том что тут немного все хитро. Так как, пользователь сам выбирает заранее подготовленные значения, так и сам вписывает их.
Например, он может выбрать цвета автомобиля, типы двигателей / кпп и тд. А вот что касается пробега, то тут он уже сам вписывает. Есть много таких значений где юзер сам вписывает данные, которые разумеются не были заранее подготовлены для выбора.

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

Таблица, где хранятся обьявления:

listings
id | title | price и тд

И вот эта таблица, которая теперь заменяет тот json:

l isting_selected_properties
listing_id | attribute_id | value_id | value_text

Теперь правда, в тех случаях где юзер сам указывает значения, поле value_id будет null а value_text содержать вписанные данные, например тот же пробег "180.000"
...
Рейтинг: 0 / 0
15.01.2018, 00:14
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584115
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Arturs NazarovsСпасибо за ответ! Дело в том что тут немного все хитро. Так как, пользователь сам выбирает заранее подготовленные значения, так и сам вписывает их.
Например, он может выбрать цвета автомобиля, типы двигателей / кпп и тд. А вот что касается пробега, то тут он уже сам вписывает. Есть много таких значений где юзер сам вписывает данные, которые разумеются не были заранее подготовлены для выбора.

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

Таблица, где хранятся обьявления:

listings
id | title | price и тд

И вот эта таблица, которая теперь заменяет тот json:

l isting_selected_properties
listing_id | attribute_id | value_id | value_text

Теперь правда, в тех случаях где юзер сам указывает значения, поле value_id будет null а value_text содержать вписанные данные, например тот же пробег "180.000"

Вот уж замена JSON на EAV - из огня да в полымя. Лучше точно не станет а вот сильно хуже на большой базе - только в путь.
А реально у вас проблема в структуре JSON неверной категорически, у вас не должно быть массива key/value а должен быть просто набор key/value вида:
{"a":1, "b":2, "c":3} тогда и по key легко будет value искать.
А у вас мало того что оно по месту будет занимать в 5 раз больше чем надо так еще и проблема доступа по key - неразрешимая почти.


--
Проект с базой но без DBA все равно что автопарк без автомеханика. Все ездит до первой поломки.
dataegret.ru
...
Рейтинг: 0 / 0
15.01.2018, 02:14
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584194
Arturs Nazarovs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arturs Nazarovs
Гость
Maxim Boguk
Вот уж замена JSON на EAV - из огня да в полымя. Лучше точно не станет а вот сильно хуже на большой базе - только в путь.
А реально у вас проблема в структуре JSON неверной категорически, у вас не должно быть массива key/value а должен быть просто набор key/value вида:
{"a":1, "b":2, "c":3} тогда и по key легко будет value искать.

Кстате да, ошибку понял. Она и правда была в структуре json. Теперь окончательно запутался и незнаю как лучше, то ли хранить данные в json, или же создать таблицу как я ранее описал. Изначально я думал что в Postgresql хранить данные в Json самое то! Да и легко делать выборку и различные JOIN'ы к связанным таблицам по неким данным внутри.

А вот с вариантом про таблицу, как мне кажется это тоже вариант. В некоторых случая EAV годен, хотя и antipatern
...
Рейтинг: 0 / 0
15.01.2018, 09:57
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584266
tip78
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tip78
Участник
во1, json B
во2, более-менее эффективный поиск там доступен только по первой ступени ключей, а глубже уже не работает
в3, если чё-то частно используемое, лучше колонку отдельную
...
Рейтинг: 0 / 0
15.01.2018, 11:43
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584456
Arturs Nazarovs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arturs Nazarovs
Гость
tip78во1, json B
во2, более-менее эффективный поиск там доступен только по первой ступени ключей, а глубже уже не работает
в3, если чё-то частно используемое, лучше колонку отдельную

Я сколько смотрел и читал про хранение данных в формате jsonb, все пишут что можно иметь огромное кол-во данных внутри и postgres отлично справляется с запросами к этим данным. Значит, без проблем можно хранить ключи/значения и даже другие данные. Но да, это вариант номер один. Плюсом будет, то что не нужно отдельную таблицу создавать и при каждом добавлении обьявлении сохранять выбранные ключи/значения в эту таблицу.

Вариант номер два, иметь отдельную таблицу для сохранения ключей/значений тоже нормальный, считаю. И пока придерживаюсь именно к этому варианту. Он более простой. А в веб разработке...чем проще, тем лучше.
...
Рейтинг: 0 / 0
15.01.2018, 14:48
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584722
tip78
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tip78
Участник
с запросами - да, а с поиском @> там не всё так гладко (со слов Бартунова)

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

также индекс GIN не работает, т.к. он только по верхним ключам создаётся:
Код: sql
1.
2.
-- Найти документы, в которых ключ "tags" содержит ключ или элемент массива "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';



авторОднако, в следующих запросах он не будет использоваться, потому что, несмотря на то, что оператор ? — индексируемый, он применяется не к индексированному столбцу jdoc непосредственно:

но это всё вроде как проапгрейдят в 10 или 11
...
Рейтинг: 0 / 0
15.01.2018, 22:23
| Ответить | Цитировать | Написать  
фильтрация данных
    #39584731
Arturs Nazarovs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arturs Nazarovs
Гость
Вообщем, попробовал создать таблицу "listing_properties", сюда будут заноситься выбранные параметры или вписанные юзером при подаче обьявления. Если выбранные параметры, то property_value будет null, а если вписанные (год, пробег), то соответственно property_value_id = null, а property_value = 180.000км.

listing_id | property_id | property_value_id| property_value

А так же, у меня существует таблица listings, куда я сохраняю поданные обьявления.

Помимо этого, есть еще некоторые таблицы, с которыми это все взаимосвязано.

А вот и запрос, который выбирает мне все обьявления и возвращает колонку в формате json с нужными значениями которые были получены при джойнах к тем другим таблицам.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT *
            from (
                select listings.*, json_object_agg(property_id::text, coalesce((listing_properties.property_value_id)::text, listing_properties.property_value)) params 
                , json_agg((json_build_object('attribute', prefix, 'value', coalesce(label, listing_properties.property_value), 'name', attrs.name, 'postfix', attr_meta.postfix, 'sort_order', meta.sort_order)) order by meta.sort_order ASC) as properties from listings
                JOIN listing_properties ON listings.id = listing_properties.listing_id
                LEFT JOIN attributes attrs ON attrs.id = listing_properties.property_id
                LEFT JOIN attribute_metas attr_meta ON attr_meta.attribute_id = listing_properties.property_id 
                LEFT JOIN value_metas attr_val ON attr_val.value_id = listing_properties.property_value_id
                LEFT JOIN category_attributes meta ON meta.attribute_id = attr_meta.attribute_id
                group by 1
                ) s




В итоге, я получа всю нужную инфу обьявлений, его атрибуты, значения и тд. Как раз, то что нужно.
...
Рейтинг: 0 / 0
15.01.2018, 22:59
| Ответить | Цитировать | Написать  
фильтрация данных
    #39595758
andruuche
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andruuche
Гость
Arturs Nazarovs,

если я правильно понял вашу задачу, то https://github.com/postgrespro/jsquery вам в помощь )

Там и гибкости фильтрации за глаза и индексирование из коробки.
...
Рейтинг: 0 / 0
02.02.2018, 16:42
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / фильтрация данных
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39584731&tid=1995963]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 17ms
total:  157ms
созд. / загр.: 157ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]