Как выбрать записи из таблицы Postgres с помощью инструкции ORDER BY в столбце jsonb, который указывает на другую таблицу

Question 1

У меня есть два стола, characteristics и students.

характеристики

 id      name          value
----    ------        -------
 1      country       england
 2      country       brazil
 3      games         football
 4      games         baseball
 5      country       india
 .        .             .
 .        .             .

Для студентов

 first_name      age       character_values
------------    -----     ------------------
  Jason          12         [1,4]
  Mark           14         [1,3] 
  Kunal          10         [5,3] 
   .              .           .
   .              .           .
   .              .           .

То characteristics таблица состоит из трех столбцов, в которых id столбец-это поле автоматического приращения. То name обозначает название конкретной характеристики и value указывает соответствующую информацию о характеристике.

То students таблица содержит подробную информацию о каждом студенте. То character_value поле в students таблица представляет собой jsonb поле. Каждый элемент в массиве jsonb имеет идентификатор, указывающий на соответствующий идентификатор характеристики в characteristics стол.

(например, если студент из страны Индия, то идентификатор 5 добавляется к character_values массив jsonb против имени соответствующего студента)

Предполагая, что каждый студент может принадлежать только к одной стране, как мне выбрать список студентов, упорядочивая результат по названию страны, к которой они принадлежат?

Question 2

Вы можете присоединиться к таблицам с jsonb_array_elements:

select c.*, s.first_name from characteristics c join students s on exists 
  (select 1 from jsonb_array_elements(s.character_values) v where v.value::int = c.id)

Выход:

ID	Имя	ценность	имя пользователя
1	Страна	Англия	Джейсон
1	Страна	Англия	Марк
3	Игры	Футбол	Марк
3	Игры	Футбол	Кунал
4	Игры	бейсбол	Джейсон
5	Страна	Индия	Кунал

Ajax1234 · Answer 1 · 2021-11-21T19:12:50

Вы можете присоединиться к таблицам с jsonb_array_elements:

select c.*, s.first_name from characteristics c join students s on exists 
  (select 1 from jsonb_array_elements(s.character_values) v where v.value::int = c.id)

Выход:

ID	Имя	ценность	имя пользователя
1	Страна	Англия	Джейсон
1	Страна	Англия	Марк
3	Игры	Футбол	Марк
3	Игры	Футбол	Кунал
4	Игры	бейсбол	Джейсон
5	Страна	Индия	Кунал

Почему вы использовали SELECT 1 вместо SELECT *? По какой-то конкретной причине?
@Vinay select 1 является ли конвенция для exists подзапросы.

Как выбрать записи из таблицы Postgres с помощью инструкции ORDER BY в столбце jsonb, который указывает на другую таблицу

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории