Select Distinct in Postgresql erroring on unrelated column

Question

I'm struggling to write an SQL statement that does the following: Grab the most recent completed assessments of a group of users

• One per user (distinct user_id)
• Of a chosen group of users (user_id in array)
• Only completed assessments (type_name is not null)
• With all fields (will use later)

Closest attempt:

SELECT DISTINCT(user_id), assessments.*
FROM assessments
WHERE
  user_id IN (1,2,3,4,5,10)
  AND type_name IS NOT NULL
ORDER BY
  created_at DESC,
  user_id DESC

Current error:

could not identify an equality operator for type json

On:

assessments.*

I've run through a lot of different versions of this getting various errors, but this one I think should work but it seems to be trying to be distinct based off of all the columns instead of just user_id.

I'd really like it to work in Rails, but since I couldn't get that, I thought getting the plain SQL to work first.

UPDATE

Part of the problem could be that I have JSON columns on assessment. But since I'm using distinct on just user_id, it should ignore those JSON columns

Answer 1

Try this:

Assesment.uniq(:user_id)
          .where(iuser_id: [1, 2, 3])
          .where
          .not(type_name: nil)

Update:

Since the problem is really in other columns than I would recommend to cast all your json columns to jsonb (pg 9.4 magic)

  class AlterJsonbToJsonAndBack < ActiveRecord::Migration                      
    def up                                                                     
      change_column :assessments, :column_name, 'jsonb USING CAST(column_name AS jsonb)'  
    end                                                                        

    def down                                                                   
      change_column :assessments, :column_name, 'json USING CAST(column_name AS json)'    
    end                                                                        
  end                                                                          

how to use jsonb in rails