Supercharge Your Ecto Queries over Postgres JSONB with Flop: Filtering, Sorting, and Pagination

Elixir's Ecto library is fantastic for interacting with your database. But when it comes to building dynamic user interfaces with features like filtering, sorting, and pagination, things can get a bit cumbersome.

Enter Flop, a powerful Elixir library designed to simplify these tasks. Flop seamlessly integrates with Ecto, allowing you to add robust filtering, sorting, and pagination capabilities to your queries with minimal effort.

Flop allows you to define custom filters for specific scenarios, providing ultimate flexibility in how you handle user input.Filter and sort data across related tables using join fields, ensuring a consistent user experience.

Postgres is master DB and its support for JSONB columns offer a flexible way to store structured data within your database, but querying them can present some challenges compared to traditional columns. Ranging from Limited Indexing, Query Complexity and Optimizer Difficulties to name a few.

Data can be extracted utilizing operators like ->>, @>, and others designed for querying JSON data. These operators allow you to navigate the JSON structure and extract specific values for filtering or sorting.

Translating such operators for dynamic queries is not straightforward. Anyone who has ever added filter would understand the work required to implement the filters, operators and avoid SQL injection attacks.

Flop works seamlessly over traditional database columns out of the box and also allows great features to work with joining fields across tables by defining filters with ease using schema configurations. Something that is missing in the docs is how to use flop query JSONB column fields.

So I reached out to the hex package author on Elixir forum and he suggested it could be done using join fields or custom fields. I opted for custom fields over join fields as it didn't made sense to me to do a self join to find a value in this case.

My initial implementation didn't work as expected and I post on elixir forum and the author provided valuable guidance over debugging the sql being generated.

Few things that were to be done differently than expected for custom fields in case of querying jsonb. Below is how you can define custom fields for a jsonb column named metadata with active and owner fields.

 adapter_opts: [
      custom_fields: [
        metadata_active: [
          filter: {MetadataFilter, :metadata, []},
          ecto_type: :boolean,
          operators: [:==]
        ],
        metadata_owner: [
          filter: {MetadataFilter, :metadata, []},
          ecto_type: :string,
          operators: [:==]
        ]
      ]
    ]

Filtering functionality can be defined as below

defmodule Maverick.Utility.MetadataFilter do
  @moduledoc """
   Maverick.Utility.MetadataFilter
  """

  import Ecto.Query

  def metadata(query, %Flop.Filter{field: name, value: value, op: op} = _flop_filter, _) do
    metadata_value = value(name, value)

    expr = dynamic_expr(name)

    case metadata_value do
      {:ok, query_value} ->
        conditions =
          case op do
            :== -> dynamic([r], ^expr == ^query_value)
            :!= -> dynamic([r], ^expr != ^query_value)
            :> -> dynamic([r], ^expr > ^query_value)
            :< -> dynamic([r], ^expr < ^query_value)
            :>= -> dynamic([r], ^expr >= ^query_value)
            :<= -> dynamic([r], ^expr <= ^query_value)
          end

        where(query, ^conditions)

      :error ->
        IO.inspect("Error casting value #{value} for #{name}")
        query
    end
  end

  def field(:metadata_active), do: :active
  def field(:metadata_owner), do: :owner

  def value(:metadata_active, value), do: Ecto.Type.cast(:boolean, value)
  def value(:metadata_owner, value), do: Ecto.Type.cast(:string, value)

  def dynamic_expr(:metadata_active) do
    dynamic(
      [r],
      fragment(
        "(?->>'active')::boolean",
        field(r, :metadata)
      )
    )
  end

  def dynamic_expr(:metadata_owner) do
    dynamic(
      [r],
      fragment(
        "(?->>'owner')",
        field(r, :metadata)
      )
    )
  end
end

The record can then be filtered via API by just adding the fields as params

<url>?filters[0][field]=name&filters[0][value]=Expert&filters[1][field]=mobile_no&filters[1][value]=88827271111&filters[2][field]=metadata_active&filters[2][value]=true

The part that was to be figured out was how dynamic fragments were getting generated as using the field name would generate SQL with it as a column rather than a filed within the metadata column.

Flop is a game-changer for building dynamic and user-friendly Elixir applications. Its seamless integration with Ecto and its rich feature set make it an essential tool for streamlining data manipulation and enhancing the user experience. So, if you're looking to simplify filtering, sorting, and pagination in your Elixir projects, Flop is definitely worth exploring.

It was great working with the package author to implement and debug such functionality. Elixir community is amazing and probably the best one out there.

Also I would like to thank ChatGPT and Gemini for outputting garbage and hallucinating solutions wasting hours of valuable time that were only recovered by the help of the author and the elixir community.