3

Postgres JSON(B) helpers for Ecto

 2 years ago
source link: https://dev.to/nspired/postgres-jsonb-helpers-for-ecto-2ib0
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Byte Sized

Posted on Oct 19

• Originally published at liftm.io

Postgres JSON(B) helpers for Ecto

If you've ever worked with JSON columns in Postgres with Ecto, you probably wrote a lot of fragments similar to this one:

fragment("?->>'field'", x.column)
Enter fullscreen modeExit fullscreen mode

to access fields in a JSON object.

This is nice, but what if we could write x.column->>field directly instead?

Well we can! We just have to define a custom operator for this. Elixir has a list of overridable operators, and even though there's no ->>, there's ~>> which looks similar enough.

defmodule JsonAccessor do
  defmacro left ~>> right do
    quote do: fragment("?->>?", unquote(left), unquote(right))
  end
end
Enter fullscreen modeExit fullscreen mode

You can then import JsonAccessor and start using this new operator:

Repo.all(from u in User, where: u.details~>>"email" == "[email protected]")
Enter fullscreen modeExit fullscreen mode

This works but... it's far from perfect:

x.column ~>> "field" # Works
x.column ~>> field # Raises "(Ecto.Query.CompileError) unbound variable `field` in query."
Enter fullscreen modeExit fullscreen mode

We also can't query nested fields without the -> operator:

x.column ~>> "field" ~>> "nested" # Won't work because `->>` returns text instead of a JSON object
Enter fullscreen modeExit fullscreen mode

We can fix the first issue by converting the right-hand side to a string when we're given an atom:

defmodule JsonAccessor do
  defmacro left ~>> right do
    quote do: fragment("?->>?", unquote(left), unquote(rhs(right)))
  end

  defp rhs({field, _ctx, nil}) when is_atom(field), do: to_string(field)
  defp rhs(field) when is_binary(field), do: field
end
Enter fullscreen modeExit fullscreen mode

Now x.column ~>> field works as expected. For the nested access, we just need to add a new operator for ->:

defmodule JsonAccessor do
  defmacro left ~>> right do
    quote do: fragment("?->>?", unquote(left), unquote(rhs(right)))
  end

  defmacro left ~> right do
    quote do: fragment("?->?", unquote(left), unquote(rhs(right)))
  end

  defp rhs({field, _ctx, nil}) when is_atom(field), do: to_string(field)
  defp rhs(field) when is_binary(field), do: field
end
Enter fullscreen modeExit fullscreen mode

And we can now write x.column~>field~>>nested == "value", instead of fragment("?->'field'->>'nested' = ?", x.column, "value") 🎉


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK