PostgreSQL's jsonb_to_recordset function with Ecto

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.

1 Like

Arent you missing a single quote here?

(?->'authors) 

Yeah, I will edit. Sorry this is a contrived example based on a real-world scenario, so its not a direct copy/paste

You cannot use AS … in a joined fragments because ecto does internally add its own AS …. Ecto maintain table aliases of queries internally. You can work around that by doing SELECT * from jsonb_to_recordset(…) AS … in the fragment.

I’m using this in a project of mine.

defmacro jsonb_recordset(types, values) do
    quote do
      fragment(
        unquote("(SELECT * FROM jsonb_to_recordset(?) as t(#{types}))"),
        ^unquote(values)
      )
    end
  end
5 Likes

Hey @LostKobrakai @michaelfich can you maybe include more context on the solution to this problem? E.g. how to use the suggested macro with a join clause

I’m running into the same issue where Ecto is adding an additional AS alias which then breaks the column definition list for the json record.

query =
      from(p in Post,
        inner_lateral_join:
          fragment("jsonb_to_recordset(?) AS t(name text, value int)", p.json_field)
      )
** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "AS"

query: SELECT count(*) FROM “posts” AS p0 INNER JOIN LATERAL jsonb_to_recordset(s0.”json_field”) AS t(name text, value int) AS f1 ON TRUE

Hey @denis, it looks like you may need to double check the example from @LostKobrakai to get yours to work.

He used a "SELECT * FROM jsonb_to_recordset" in his fragment, as opposed to simply joining the jsonb_to_recordset as you and I did in our examples.

I think this would work for you:

query =
  from post in Post,
    inner_lateral_join: fragment(
      "(SELECT * FROM jsonb_to_recordset(?) AS t(name text, value int))",
      post.json_field
    )
1 Like

thanks @michaelfich, I tried it with SELECT * FROM jsonb_to_recordset(...) and it gets rid of that double AS alias problem that we both ran into. It confused me a bit because it’s not necessary in plain SQL.

Curious if you got the macro version to work too? I’ll try that next.

Cheers!

Did you make sure to include the parenthesis around the raw SQL code, as seen in @LostKobrakai’s example?

fragment("(SELECT * FROM jsonb_to_recordset(?) AS t(name text, value int))")

They are important, otherwise you will run into the issue with two AS in the same part of the join.