Como selecionar registros de uma tabela Postgres usando uma instrução ORDER BY em um jsonb coluna que aponta para outra tabela

0

Pergunta

Eu tenho duas tabelas, characteristics e students.

características

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

alunos

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

O characteristics tabela possui três colunas, onde o id coluna é um campo de incremento automático. O name indica o nome de uma característica específica e a value indica a respectiva informação sobre a característica.

O students a tabela contém detalhes sobre cada aluno. O character_value de campo no students tabela é uma jsonb de campo. Cada elemento na jsonb matriz é uma IDENTIFICAÇÃO que aponta para a adequada característica de IDENTIFICAÇÃO do characteristics tabela.

(por exemplo, se um estudante é o país da índia, em seguida, o ID 5 é acrescentado para character_values jsonb matriz contra o respectivo nome do aluno)

Supondo-se que cada aluno pode pertencer a apenas um país, como faço para selecionar de uma lista de alunos, enquanto a ordenação do resultado com o nome do país a que pertencem?

database jsonb postgresql sql
2021-11-21 18:58:49
1

Melhor resposta

1

Você pode associar as tabelas com 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)

Saída:

id nome valor first_name
1 país inglaterra Jason
1 país inglaterra Marca
3 jogos futebol Marca
3 jogos futebol Kunal
4 jogos beisebol Jason
5 país índia Kunal
2021-11-21 19:12:50

Por que você usar um SELECT 1 em vez de SELECT *? Alguma razão específica?
Vinay

@Vinay select 1 a convenção para exists subconsultas.
Ajax1234

Em outros idiomas

Esta página está em outros idiomas

Русский
..................................................................................................................
Italiano
..................................................................................................................
Polski
..................................................................................................................
Română
..................................................................................................................
한국어
..................................................................................................................
हिन्दी
..................................................................................................................
Français
..................................................................................................................
Türk
..................................................................................................................
Česk
..................................................................................................................
ไทย
..................................................................................................................
中文
..................................................................................................................
Español
..................................................................................................................
Slovenský
..................................................................................................................