How to select records from a Postgres table using an ORDER BY statement on a jsonb column that points to another table

I have two tables, characteristics and students.

characteristics

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

students

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

The characteristics table has three columns where the id column is an auto-increment field. The name denotes the name of a specific characteristic and the value indicates the respective information about the characteristic.

The students table contains detail about each student. The character_value field in the students table is a jsonb field. Each element in the jsonb array is an ID that points to the appropriate characteristic ID in the characteristics table.

(for example, if a student is from the country of india then the ID 5 is appended to character_values jsonb array against the respective student’s name)

Assuming that each student can only belong to one country, how do I select a list of students while ordering the result by the name of the country they belong to?

Answer

You can join the tables with 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	name	value	first_name
1	country	england	Jason
1	country	england	Mark
3	games	football	Mark
3	games	football	Kunal
4	games	baseball	Jason
5	country	india	Kunal