How to join a class with self column of it which is of jsonb type

Question

I have a class named 'School' with the following schema

table "school" do |t|
  t.uuid "school_name"
  t.string "city"
  t.jsonb "students"
end

student column of the above table is of jsonb type. Currently my table looks like this -

School table

Now, I want to perform a join in such way that I have the records in following form -

Desired result

I can achieve this using flatten function in snowflake -

select school.*, student.value from school, table(flatten(students)) as student

How can I do this in psql or Active record in rails

I tried different cross joins but it didn't work. I am trying since 5 hrs to get desired result but I am still facing this issue.

Answer 1

Your table design should be changed in order to better map the real-world conditions. There may be many schools in a city, and a given school may have many students. So the appropriate relationships are:

class City < ApplicationRecord
  # has columns: id, name
  has_many :schools
end

class School < ApplicationRecord
  # has columns id, city_id, name
  belongs_to :city
  has_many :students
end

class Student < ApplicationRecord
  # has columns id, school_id, name, date_of_birth
  belongs_to :school
end

Then the query that produces the desired result is:

# student.rb
def self.list_all
  select("students.name, students.date_of_birth, schools.name, schools.id, city.name").
  joins(school: :city)
end

By the way, it doesn't make sense to store a student's age, b/c it changes with time, if you want that in your view, calculate it from date_of_birth.