Setting Empty-String JSONB Values to Null When Sorting with Ransack

Using the Ransack gem is a great way to add search functionality to your application without the need for additional libraries or dependencies.

One issue you may have is when sorting data by an attribute that is nested in a JSON column. If that attribute’s value is an empty string, it will be included in the sorted list (even when we’ve declared :nulls_last in our Ransack configuration).

In this post, I’ll be covering how to ensure that empty-string JSONB values are treated as null values and placed at the end of any sorted list when searching and sorting with Ransack.

Before we get started, here’s some sample data we’ll be using:

Note: for Member #3 and Member #4 the empty string for location.

Our goal here is to sort our Members table by location, and to ensure that any empty-string Location values are treated as null values in the sorted list.

More specifically, we’ll need the resulting SQL query generated by Ransack to look like this:

{% c-block language="sql" %}

SELECT DISTINCT "members".*, NULLIF("members"."data" ->> 'location', ")
FROM "members"
ORDER BY NULLIF("members"."data" ->> 'location', '') ASC NULLS LAST

{% c-block-end %}

‍
Sorting by JSONB nested attributes using Ransack

ransackers: special methods provided by Ransack for creating additional search functions via Arel

By taking advantage of ransackers, we can sort our table by the members’ Location JSONB attributes.

Let’s add the following to our Member model:

{% c-block language="ruby" %}

class Member < ApplicationRecord
   # ...

   ransacker :location do |parent|
 Arel::Nodes::InfixOperation.new(
    '->>',
    parent.table[:data],
    Arel::Nodes::Quoted.new(‘location’)
 )
end

     # ...
end

{% c-block-end %}

The ransacker above ensures that a member’s location (a nested JSONB attribute) is available as a top-level attribute so that we can build a table that can be sorted by members’ locations.

Our Ransack result is defined as:

{% c-block language="ruby" %}

def search_result

members = Member.arel_table
data_column = members[:data]

workspace
.members
.select(members[Arel.star], jsonb_attr(data_column, 'location'))
.ransack(ransack_filter)
.result(distinct: true)
end

def jsonb_attr(column, attr)
Arel::Nodes::InfixOperation.new(
'->>',
column,
Arel::Nodes::Quoted.new(attr.to_s)
end

{% c-block-end %}

With the code above, we are making the Location JSONB attribute available in the SELECT statement of our SQL query:

{% c-block language="sql" %}

SELECT DISTINCT "members".*, "members"."data" ->> 'location'
FROM "members"

{% c-block-end %}

When we sort our table by Location, it works. Kind of.

The issue is that the empty-string Location values have been placed at the beginning of the sorted list:

For one record, this may not be a big deal.

But consider dozens of records where the member’s Location attribute is an empty string. In that case, a user would need to sift through several blank Location values just to get to the beginning of the alphabetized list.

Setting NULLS LAST doesn’t account for empty-string values

According to the Ransack docs, we can use :nulls_first or :nulls_last to decide if we want null values to appear before or after non-null values when sorting.

We can also force nulls to always be first or last by using :nulls_always_first or :nulls_always_last.

While this is helpful, it doesn’t apply to the empty-string values that may be present in a JSONB column.

What to do? We need to set any empty-string values in the Location column as null values so that they’re sorted after the non-null values.

Setting Empty-String JSONB Values to null values

The method below will allow us to take any empty-string value and return it as NULL when Ransack builds our #search_results query:

{% c-block language="ruby" %}

def null_if(column, value)
  Arel::Nodes::NamedFunction.new "NULLIF", [column, value]
end

{% c-block-end %}

In our code, we can place this method below our jsonb_attr method:

{% c-block language="ruby" %}

def jsonb_attr(column, attr)
Arel::Nodes::InfixOperation.new(
'->>',
column,
Arel::Nodes::Quoted.new(attr.to_s)
)
end

def null_if(column, value)
Arel::Nodes::NamedFunction.new "NULLIF", [column, value]
end

{% c-block-end %}

Next, we need to update our jsonb_attr method by wrapping the method’s code in the null_if helper:

{% c-block language="ruby" %}

def jsonb_attr(column, attr)
null_if(
           Arel::Nodes::InfixOperation.new('->>', column, Arel::Nodes::Quoted.new(attr.to_s)),
           Arel::Nodes::Quoted.new('')
       )
end

{% c-block-end %}

For the null_if arguments above, the column is the InfixOperation object, and the value is an empty string built by the Arel::Nodes::Quoted class.

Updating the Location ransacker to return a NULLIF ORDER BY clause

Now, if I try to sort by Location in the table, I get a Postgres error:

{% c-block language="sql" %}

ActiveRecord::StatementInvalid (PG::InvalidColumnReference: ERROR: 
for SELECT DISTINCT, ORDER BY expressions must appear in select list)

{% c-block-end %}

We’re seeing this error because we are trying to ORDER BY the following:

{% c-block language="sql" %}

ORDER BY "members"."data" ->> 'location'

{% c-block-end %}

‍

When what is stated in the SELECT statement is the following:

{% c-block language="sql" %}

SELECT DISTINCT … NULLIF("members"."data" ->> 'location', '')

{% c-block-end %}

Since ORDER BY expressions need to be the same expressions set in the SELECT list, we need to make sure that the NULLIF statement is present in the ORDER BY clause.

To do this, let’s update our Location ransacker on the Member model:

{% c-block language="ruby" %}

class Member < ApplicationRecord
   # ...
   ransacker :location do |parent|
Arel::Nodes::NamedFunction.new 'NULLIF',
                                  [
                                    Arel::Nodes::InfixOperation.new(
                                      '->>',
                                      parent.table[:data],
                                      Arel::Nodes::Quoted.new('location')
                                    ),
                                    Arel::Nodes::Quoted.new('')
                                  ]
end
   
   #...
end

{% c-block-end %}

Here, we are taking the code within our null_if method and using it explicitly in our ransacker in the Member model.

The null_if method takes two arguments: column (the InfixOperation object) and value (the Quoted object).

Adding the null_if wrapper to the Location ransacker allows us to ORDER BY that same NULLIF(...) argument that we’ve stated in our SELECT statement (via our Ransack query).

Here’s what the resulting query looks like:

{% c-block language="sql" %}

SELECT DISTINCT "members".*, NULLIF("members"."data" ->> 'location', ")
FROM "members"
ORDER BY NULLIF("members"."data" ->> 'location', '') ASC NULLS LAST

{% c-block-end %}

And there you have it! With this, the resulting sorts by location place the empty string values at the end of list, no matter the sorting direction.

Conclusion

This post covered how to set empty-string values of a JSONB column as null values when using the Ransack gem to search and sort data.

I hope this helps in all of your table-sorting endeavors that involve Ransack, JSONB nested attributes, and null values!

—

Thanks to @hadees on GitHub for a useful Arel gist that provided the null_if method we used in this post.

‍