Гость
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса со ссылочными полями в JSON / 9 сообщений из 9, страница 1 из 1
03.06.2020, 02:57
    #39965502
Big Cheese
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Здравствуйте!
Помогите грамотно составить SQL-запрос.

Имеется таблица tab, в которой имеется поле data типа JSONB.
В структуре JSON есть два общих атрибута:
  • title (наименование),
  • ref (ссылка, содержит ID другой записи в той же таблице tab)
предварительно еще отфильтровав этот title по некоторому условию, и возможно выполнить сортировку по этому атрибуту.

Грубо получается примерно следующий запрос
Код: sql
1.
2.
3.
4.
select (select data #>> '{title}' from tab where id::varchar = t.data #>> '{ref}') as title
from tab t
where (select data #>> '{title}' from tab where id::varchar = t.data #>> '{ref}') like 'xyz%'
order by title



Здесь один подзапрос дублируется и в выражении select и в where.
Возможно ли как то более оптимально составить запрос?
...
Рейтинг: 0 / 0
03.06.2020, 12:44
    #39965616
Swa111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Big Cheese,

а на tab верхнего уровня ни каких больше условий не накладывается кроме заголовка от ссылки? Странный запрос...
...
Рейтинг: 0 / 0
03.06.2020, 13:12
    #39965626
Big Cheese
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Конечно, в таблице есть и другие поля, и возможны другие условия.
Я привел максимально упрощенный запрос.
В поле data хранятся табличные данные, и по аналогии с таблицей БД, у них тоже могут быть ссылки на другие записи.
Соответственно, фильтрация и сортировка осуществляется над атрибутами JSON поля data.
Почему это все хранится в JSON, а не в таблице БД? Это сделано для возможности динамического создания пользователем таблиц с произвольной схемой.
...
Рейтинг: 0 / 0
03.06.2020, 13:37
    #39965641
mad_nazgul
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Big Cheese
Конечно, в таблице есть и другие поля, и возможны другие условия.
Я привел максимально упрощенный запрос.
В поле data хранятся табличные данные, и по аналогии с таблицей БД, у них тоже могут быть ссылки на другие записи.
Соответственно, фильтрация и сортировка осуществляется над атрибутами JSON поля data.
Почему это все хранится в JSON, а не в таблице БД? Это сделано для возможности динамического создания пользователем таблиц с произвольной схемой.


Прошу прощения, за такое архитектурное решение надо руки отрывать. :-)

Если вам нужно динамическое создание таблиц, почему бы их просто не создавать в БД?!
...
Рейтинг: 0 / 0
03.06.2020, 15:31
    #39965744
Big Cheese
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Потому что система должна знать, что создал пользователь, структуру этих таблиц, уметь хранить там данные, делать выборку из таких таблиц и строить отчеты. В принципе можно обойтись и без JSON, но все равно это будет несколько взаимосвязанных таблиц, хранящие разные метаданные пользовательской таблицы. Хранение в JSON - это альтернативный вариант, хуже он или лучше, это другой вопрос, но я бы не стал тут отрывать руки.
...
Рейтинг: 0 / 0
03.06.2020, 16:13
    #39965783
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Big Cheese
Потому что система должна знать, что создал пользователь, структуру этих таблиц, уметь хранить там данные, делать выборку из таких таблиц и строить отчеты. В принципе можно обойтись и без JSON, но все равно это будет несколько взаимосвязанных таблиц, хранящие разные метаданные пользовательской таблицы. Хранение в JSON - это альтернативный вариант, хуже он или лучше, это другой вопрос, но я бы не стал тут отрывать руки.


Это нельзя сделать так чтобы быстро работало.
Или гибко или быстро... тут уж вам выбирать.
...
Рейтинг: 0 / 0
04.06.2020, 05:30
    #39965966
mad_nazgul
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Big Cheese
Потому что система должна знать, что создал пользователь, структуру этих таблиц, уметь хранить там данные, делать выборку из таких таблиц и строить отчеты. В принципе можно обойтись и без JSON, но все равно это будет несколько взаимосвязанных таблиц, хранящие разные метаданные пользовательской таблицы. Хранение в JSON - это альтернативный вариант, хуже он или лучше, это другой вопрос, но я бы не стал тут отрывать руки.


Максимум, что нужно хранить, это кто какую таблицу создал/изменил.
Все остальное, это эмуляция БД в БД.
Зачем?!

SQL - это очень гибкий ЯП, для манипулирования данными.
Нужно создать БД - создавайте через DDL.

Зачем создавать свой глючный велосипед?!

Либо, использовать NoSQL, ту же Mongo.
Которая как раз заточена под акой сценарий использования.
...
Рейтинг: 0 / 0
10.06.2020, 00:16
    #39967650
Troglodit
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
Код: sql
1.
2.
3.
select data #>> '{title}' as title from tab where
exists(select  * from tab t where  tab.id::varchar = t.data #>> '{ref}') and data #>> '{title}'  like 'xyz%'
order by title


Может так?
...
Рейтинг: 0 / 0
10.06.2020, 08:30
    #39967689
crutchmaster
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса со ссылочными полями в JSON
mad_nazgul
SQL - это очень гибкий ЯП

Sql не очень прям гибкий. Гнуть данные на какой-нибудь ноде в виде массива объектов куда проще. У sql другие преимущества.
...
Рейтинг: 0 / 0
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса со ссылочными полями в JSON / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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