Custom filters on jsonb column using Flop

darnahsan

I am trying to implement custom filters on a table that has a jsonb column metadata using Flop. as per @woylie suggestions trying to use custom fields

Following the docs for custom fields came up with below code.

when trying to lookup metadata_is_active getting error

Postgrex expected a binary, got true. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

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

for metadata_owner it throws no error just returns empty result even on values that should match

<url>?filters[0][field]=name&filters[0][value]=Expert&filters[1][field]=whatsapp_no&filters[1][value]=88827271111&filters[2][field]=metadata_owner&filters[2][value]=abc

I feel my noobness with dynamic and fragmented queries is probably where I am wrong.

organization.ex

@derive {
    Flop.Schema,
    filterable: [:name, :whatsapp_no, :metadata_is_active, :metadata_owner],
    sortable: [:name, :whatsapp_no, :inserted_at],
    max_limit: 100,
    default_limit: 50,
    adapter_opts: [
      custom_fields: [
        metadata_is_active: [
          filter: {OrganizationMetadataFilters, :metadata, []},
          ecto_type: :boolean
        ],
        metadata_owner: [
          filter: {OrganizationMetadataFilters, :metadata, []},
          ecto_type: :string
        ]
      ]
    ]
  }

  schema "organizations" do
    field :name, :string
    field :whatsapp_no, :string

    embeds_one :metadata, OrganizationMetadata

organization_metadata_filters.ex

defmodule Maverick.Organizations.OrganizationMetadataFilters do
  @moduledoc """
   Maverick.Organizations.MetadataFilters
  """

  import Ecto.Query

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

    expr =
      dynamic(
        [r],
        fragment(
          "metadata->>'?'",
          field(r, ^metadata_field)
        )
      )

    case metadata_value do
      {:ok, query_value} ->
        IO.inspect(flop_filter, label: "Flop filter")
        IO.inspect(metadata_field, label: "Metadata field")
        IO.inspect(query_value, label: "Metadata 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

        IO.inspect(conditions, label: "conditions")

        where(query, ^conditions)

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

  def field(:metadata_is_active), do: :is_active
  def field(:metadata_owner), do: :owner

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

13 comments

/phoenix /flop #ecto #troubleshooting

0 1348 13

2024-09-23 10:00:40 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

woylie

As per documentation, ->> gets an object field as text, but you try to pass a boolean. Try changing ecto_type to :string.

Post #1

darnahsan

Thanks for the link. With explicit type casting the 1st error is resolved but now I see the same empty result for both fields which mean they are not matching somehow.

Also the wierd part is I see that metadata function is called twice for each request, is that expected ?

defmodule Maverick.Organizations.OrganizationMetadataFilters do
  @moduledoc """
   Maverick.Organizations.MetadataFilters
  """

  import Ecto.Query

  def metadata(query, %Flop.Filter{field: name, value: value, op: op} = flop_filter, _) do
    IO.inspect(DateTime.utc_now(), label: "Timestamp")
    metadata_field = field(name)
    metadata_value = value(name, value)

    expr = dynamic_expr(name, metadata_field)

    case metadata_value do
      {:ok, query_value} ->
        IO.inspect(flop_filter, label: "Flop filter")
        IO.inspect(metadata_field, label: "Metadata field")
        IO.inspect(query_value, label: "Metadata 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

        IO.inspect(conditions, label: "conditions")

        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, metadata) do
    dynamic(
      [r],
      fragment(
        "(metadata->>'?')::boolean",
        field(r, ^metadata)
      )
    )
  end

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

Post #2

woylie

With explicit type casting the 1st error is resolved but now I see the same empty result for both fields which mean they are not matching somehow.

Could you output the actual SQL? (Flop.validate → Flop.query → Ecto.Adapters.SQL.to_sql)

Also the wierd part is I see that metadata function is called twice for each request, is that expected ?

If you use offset/page-based pagination, Flop makes a second query to retrieve the total count. The parameters are only validated once, but the where clauses are built once for the main query and once for the count query.

Post #3

darnahsan

Seems there is an error in Flop.validate when sending boolean value which then doesn’t add the custom fields to the query, hence no results. Below is the output

Flop.validate

{:error,
 %Flop.Meta{
   backend: nil,
   current_offset: nil,
   current_page: nil,
   end_cursor: nil,
   next_offset: nil,
   next_page: nil,
   page_size: nil,
   previous_offset: nil,
   previous_page: nil,
   schema: nil,
   start_cursor: nil,
   total_count: nil,
   total_pages: nil,
   errors: [
     filters: [
       [],
       [],
       [
         field: [
           {"is invalid",
            [type: Flop.CustomTypes.ExistingAtom, validation: :cast]}
         ]
       ]
     ]
   ],
   flop: %Flop{
     after: nil,
     before: nil,
     first: nil,
     last: nil,
     limit: nil,
     offset: nil,
     order_by: nil,
     order_directions: nil,
     page: nil,
     page_size: nil,
     decoded_cursor: nil,
     filters: []
   },
   has_next_page?: false,
   has_previous_page?: false,
   opts: [],
   params: %{
     "filters" => [
       %{"field" => "name", "value" => "Expert"},
       %{"field" => "whatsapp_no", "value" => "88827271111"},
       %{"field" => "metadata_active", "value" => "true"}
     ],
     "order_by" => ["name"],
     "order_directions" => ["asc"],
     "page" => "1",
     "page_size" => "3"
   }
 }}

Flop.query → → Ecto.Adapters.SQL.to_sql

Flop RAW SQL: {"SELECT o0.\"id\", o0.\"name\", o0.\"whatsapp_no\", o0.\"metadata\", o0.\"inserted_at\", o0.\"updated_at\" FROM \"organizations\" AS o0",
 []}

For the string field there is no error and query seem ok

Flop validate:

{:ok,
 %Flop{
   after: nil,
   before: nil,
   first: nil,
   last: nil,
   limit: nil,
   offset: nil,
   order_by: [:name],
   order_directions: [:asc],
   page: 1,
   page_size: 3,
   decoded_cursor: nil,
   filters: [
     %Flop.Filter{field: :name, op: :==, value: "Expert"},
     %Flop.Filter{field: :whatsapp_no, op: :==, value: "88827271111"},
     %Flop.Filter{field: :metadata_owner, op: :==, value: "Muneeb"}
   ]
 }}

Flop.query -> → Ecto.Adapters.SQL.to_sql

{"SELECT o0.\"id\", o0.\"name\", o0.\"whatsapp_no\", o0.\"metadata\", o0.\"inserted_at\", o0.\"updated_at\" FROM \"organizations\" AS o0 WHERE (o0.\"name\" = $1) AND (o0.\"whatsapp_no\" = $2) AND ((metadata->>'o0.\"owner\"') = $3) ORDER BY o0.\"name\" LIMIT $4 OFFSET $5",
 ["Expert", "88827271111", "Muneeb", 3, 0]}

Post #4

woylie

The schema config in your first post uses metadata_is_active as custom field name, but the parameters use metadata_active.

Post #5

darnahsan

I updated it from is_active to active to match the key in jsonb, its active everywhere now

Post #6

darnahsan

on changing the ecto_type to string in Flop schema it works fine as the incoming param from request is a string

@derive {
    Flop.Schema,
    filterable: [:name, :whatsapp_no, :metadata_active, :metadata_owner],
    sortable: [:name, :whatsapp_no, :inserted_at],
    max_limit: 100,
    default_limit: 50,
    adapter_opts: [
      custom_fields: [
        metadata_active: [
          filter: {OrganizationMetadataFilters, :metadata, []},
          ecto_type: :string
        ],
        metadata_owner: [
          filter: {OrganizationMetadataFilters, :metadata, []},
          ecto_type: :string
        ]
      ]
    ]
  }

Flop validate:

{:ok,
 %Flop{
   after: nil,
   before: nil,
   first: nil,
   last: nil,
   limit: nil,
   offset: nil,
   order_by: [:name],
   order_directions: [:asc],
   page: 1,
   page_size: 3,
   decoded_cursor: nil,
   filters: [
     %Flop.Filter{field: :name, op: :==, value: "Expert"},
     %Flop.Filter{field: :whatsapp_no, op: :==, value: "88827271111"},
     %Flop.Filter{field: :metadata_active, op: :==, value: "true"}
   ]
 }}

Flop RAW SQL

{"SELECT o0.\"id\", o0.\"name\", o0.\"whatsapp_no\", o0.\"metadata\", o0.\"inserted_at\", o0.\"updated_at\" FROM \"organizations\" AS o0 WHERE (o0.\"name\" = $1) AND (o0.\"whatsapp_no\" = $2) AND ((metadata->>'o0.\"active\"')::boolean = $3) ORDER BY o0.\"name\" LIMIT $4 OFFSET $5",
 ["Expert", "88827271111", true, 3, 0]}

Post #7

darnahsan

shouldn’t this be

something like this ?
o0.metadata->>'active'

similarly

(metadata->>'o0."owner"') as (o0.metadata->>'owner')

Post #8

darnahsan

doing so fixes it for now

 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

Flop RAW SQL

{"SELECT o0.\"id\", o0.\"name\", o0.\"whatsapp_no\", o0.\"metadata\", o0.\"inserted_at\", o0.\"updated_at\" FROM \"organizations\" AS o0 WHERE (o0.\"name\" = $1) AND (o0.\"whatsapp_no\" = $2) AND ((o0.\"metadata\"->>'active')::boolean = $3) ORDER BY o0.\"name\" LIMIT $4 OFFSET $5",
 ["Expert", "88827271111", true, 3, 0]}

Thanks for you help @woylie , I have another query for :in op, will discuss separately

Post #9

balanza

Hi @darnahsan,

thanks for starting this thread, it’s currently the only example reference for querying JSONB with Flop.

I created a small project implementing your example, I hope it doesn’t bother you. You can find it at GitHub - balanza/example-elixir-flop-postgres-jsonb · GitHub.

I tried to fill the gaps with some boilerplate code. However, my implementation doesn’t work: the custom filter is not invoked.

If you want to try:

iex -S mix

iex(1)> MyApp.exec
----->Parsed filters:
%Flop{
  after: nil,
  before: nil,
  first: nil,
  last: nil,
  limit: 50,
  offset: nil,
  order_by: nil,
  order_directions: nil,
  page: nil,
  page_size: nil,
  decoded_cursor: nil,
  filters: [%Flop.Filter{field: :metadata_owner, op: :==, value: "anyvalue"}]
}
----->Generated query:
#Ecto.Query<from o0 in MyApp.Organization,
 where: o0.metadata_owner == ^"anyvalue", limit: ^50>
#Ecto.Query<from o0 in MyApp.Organization,
 where: o0.metadata_owner == ^"anyvalue", limit: ^50>
iex(2)>

As you can see, the field is evalued just like it belongs to the original Organization schema.

Post #10

Load more posts (3 remaining)