I have a database with a JSONB field in one of the schemas of the database I’m working with. Up until, the data stored in the JSONB was mostly for audit purposes however I now find myself needing to query it for a part of the application.
Below is a obfuscated version of the data that I’m working with. Obviously in the scenario below there would be better ways to data model it, but the part that I want to illustrate here is that the JSONB field is an object where one of its fields is an array of objects.
In this example, I’m looking to write a query where I can find all books where one of its authors has the last name “Orwell”.
Migration
def change do
create table(:books) do
add :metadata, :jsonb
end
end
Schemas
defmodule Book do
use Ecto.Schema
schema :books do
embeds_one :metadata, BookMetadata
end
end
defmodule BookMetadata do
use Ecto.Schema
embedded_schema do
field :published_at, :date
field :genre, :string
embeds_many :authors, BookAuthor
end
end
defmodule BookAuthor do
use Ecto.Schema
embedded_schema do
field :first_name, :string
field :last_name, :string
field :birthdate, :date
field :website, :string
end
end
I’ve been able to write direct SQL queries against my database which can properly scope the query to fetch data. The first approach was this:
SELECT b
FROM books b
INNER JOIN LATERAL jsonb_to_recordset(b.metadata->'authors')
AS r(first_name varchar, last_name varchar, birthdate date, website varchar)
ON true
WHERE last_name = 'Orwell';
I then attempted to turn this into an Ecto query using a fragment like this:
from b in Book,
inner_lateral_join: fragment("jsonb_to_recordset(?->'authors') AS r(first_name varchar, last_name varchar, birthdate date, website varchar)")
where: fragment("last_name = ?", "Orwell"),
select: b
When trying to pass this query to the Repo.all/2
function, it fails with the following error.
** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "AS"
After looking at the raw SQL that was the Ecto query was being translated into, this makes sense as Ecto is adding its own alias with AS
to each SELECT
and JOIN
.
I’m curious if anyone else has had any success composing an Ecto query using jsonb_to_recordset
. If so, I’d be very grateful for any advice.