Come selezionare i record da un Postgres tabella utilizzando un'istruzione ORDER BY su un jsonb colonna che punti a un'altra tabella

Question 1

Ho due tabelle, characteristics e students.

caratteristiche

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

gli studenti

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

Il characteristics tabella con tre colonne in cui il id colonna è un campo a incremento automatico. Il name indica il nome di una caratteristica specifica e la value indica le rispettive informazioni circa le caratteristiche.

Il students tabella contiene il dettaglio di ogni studente. Il character_value campo in students la tabella è un jsonb campo. Ogni elemento della jsonb array è un ID che punti appropriato caratteristica ID characteristics tabella.

(per esempio, se uno studente è il paese dell'india, quindi l'ID 5 è aggiunto character_values jsonb array contro il rispettivo nome dello studente)

Supponendo che ogni studente può appartenere solo a un paese, come faccio a selezionare un elenco di studenti, mentre per ordinare i risultati per nome del paese a cui appartengono?

Question 2

È possibile unire le tabelle con 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)

Output:

id	nome	valore	first_name
1	paese	inghilterra	Jason
1	paese	inghilterra	Mark
3	giochi	calcio	Mark
3	giochi	calcio	Kunal
4	giochi	baseball	Jason
5	paese	india	Kunal

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

È possibile unire le tabelle con 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)

Output:

id	nome	valore	first_name
1	paese	inghilterra	Jason
1	paese	inghilterra	Mark
3	giochi	calcio	Mark
3	giochi	calcio	Kunal
4	giochi	baseball	Jason
5	paese	india	Kunal

Perché l'hai fatto a SELEZIONARE 1 invece di SELEZIONARE *? Per qualsiasi motivo?

Come selezionare i record da un Postgres tabella utilizzando un'istruzione ORDER BY su un jsonb colonna che punti a un'altra tabella

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria