Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Оптимизация запроса со ссылочными полями в JSON  [new]
Big Cheese
Member

Откуда:
Сообщений: 5
Здравствуйте!
Помогите грамотно составить SQL-запрос.

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

Задача вывести атрибут title записи по ссылке, указанной в атрибуте ref,
предварительно еще отфильтровав этот title по некоторому условию, и возможно выполнить сортировку по этому атрибуту.

Грубо получается примерно следующий запрос
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.
Возможно ли как то более оптимально составить запрос?
3 июн 20, 02:57    [22144616]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
Swa111
Member

Откуда:
Сообщений: 175
Big Cheese,

а на tab верхнего уровня ни каких больше условий не накладывается кроме заголовка от ссылки? Странный запрос...
3 июн 20, 12:44    [22144819]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
Big Cheese
Member

Откуда:
Сообщений: 5
Конечно, в таблице есть и другие поля, и возможны другие условия.
Я привел максимально упрощенный запрос.
В поле data хранятся табличные данные, и по аналогии с таблицей БД, у них тоже могут быть ссылки на другие записи.
Соответственно, фильтрация и сортировка осуществляется над атрибутами JSON поля data.
Почему это все хранится в JSON, а не в таблице БД? Это сделано для возможности динамического создания пользователем таблиц с произвольной схемой.
3 июн 20, 13:12    [22144838]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
mad_nazgul
Member

Откуда:
Сообщений: 5358
Big Cheese
Конечно, в таблице есть и другие поля, и возможны другие условия.
Я привел максимально упрощенный запрос.
В поле data хранятся табличные данные, и по аналогии с таблицей БД, у них тоже могут быть ссылки на другие записи.
Соответственно, фильтрация и сортировка осуществляется над атрибутами JSON поля data.
Почему это все хранится в JSON, а не в таблице БД? Это сделано для возможности динамического создания пользователем таблиц с произвольной схемой.


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

Если вам нужно динамическое создание таблиц, почему бы их просто не создавать в БД?!
3 июн 20, 13:37    [22144859]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
Big Cheese
Member

Откуда:
Сообщений: 5
Потому что система должна знать, что создал пользователь, структуру этих таблиц, уметь хранить там данные, делать выборку из таких таблиц и строить отчеты. В принципе можно обойтись и без JSON, но все равно это будет несколько взаимосвязанных таблиц, хранящие разные метаданные пользовательской таблицы. Хранение в JSON - это альтернативный вариант, хуже он или лучше, это другой вопрос, но я бы не стал тут отрывать руки.
3 июн 20, 15:31    [22145001]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4170
Big Cheese
Потому что система должна знать, что создал пользователь, структуру этих таблиц, уметь хранить там данные, делать выборку из таких таблиц и строить отчеты. В принципе можно обойтись и без JSON, но все равно это будет несколько взаимосвязанных таблиц, хранящие разные метаданные пользовательской таблицы. Хранение в JSON - это альтернативный вариант, хуже он или лучше, это другой вопрос, но я бы не стал тут отрывать руки.


Это нельзя сделать так чтобы быстро работало.
Или гибко или быстро... тут уж вам выбирать.
3 июн 20, 16:13    [22145060]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
mad_nazgul
Member

Откуда:
Сообщений: 5358
Big Cheese
Потому что система должна знать, что создал пользователь, структуру этих таблиц, уметь хранить там данные, делать выборку из таких таблиц и строить отчеты. В принципе можно обойтись и без JSON, но все равно это будет несколько взаимосвязанных таблиц, хранящие разные метаданные пользовательской таблицы. Хранение в JSON - это альтернативный вариант, хуже он или лучше, это другой вопрос, но я бы не стал тут отрывать руки.


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

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

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

Либо, использовать NoSQL, ту же Mongo.
Которая как раз заточена под акой сценарий использования.
4 июн 20, 05:30    [22145426]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
Troglodit
Member

Откуда:
Сообщений: 501
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

Может так?

Сообщение было отредактировано: 10 июн 20, 00:14
10 июн 20, 00:16    [22148440]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса со ссылочными полями в JSON  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 1349
mad_nazgul
SQL - это очень гибкий ЯП

Sql не очень прям гибкий. Гнуть данные на какой-нибудь ноде в виде массива объектов куда проще. У sql другие преимущества.

Сообщение было отредактировано: 10 июн 20, 08:28
10 июн 20, 08:30    [22148509]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить