Ecto - Why doesn't `Jason.encode` work for interpolating in `jsonb` queries?

Questions / Help
, ,
1

I cannot figure out why this test is failing. It fails on assert result_one == result_two. The hardcoded string sigil query returns a list of length 1, the interpolated query returns an empty list.

The Jason.encode’d struct is apparently the same as the hardcoded string sigil.

The schema of Job isn’t that important, just that custom_fields is a jsonb column.

    test "json" do
      intern = %{"Employment Type" => "Intern"}
      insert(:job, custom_fields: intern)

      intern_json = Jason.encode!(intern)
      assert intern_json === ~s|{"Employment Type":"Intern"}| # This passes fine
      query =
        from j in Job,
        as: :job,
        where: fragment("custom_fields @> ?", ^intern_json),
        select: j.id

      query_two =
        from j in Job,
        where: fragment("custom_fields @> ?", ~s|{"Employment Type":"Intern"}|),
        select: j.id

      result_one = Repo.all(query)
      result_two = Repo.all(query_two)

      assert result_one == result_two # Failure here
    end

Result:

     Assertion with == failed
     code:  assert result_one == result_two
     left:  []
     right: ["job_id0"]

Thanks for all the help

2

Can you run both queries through MyApp.Repo.to_sql and see if they’re somehow different?

3 
{"SELECT j0.\"id\" FROM \"jobs\" AS j0 WHERE (custom_fields @> $1)",
 ["{\"Employment Type\":\"Intern\"}"]}
{"SELECT j0.\"id\" FROM \"jobs\" AS j0 WHERE (custom_fields @> '{\"Employment Type\":\"Intern\"}')",

The second query is surrounded in single quotes but I’m not sure if that’s just what it would look like if it wasn’t interpolated?

4

That is the difference though. With a hardcoded value ecto doesn’t parameterize the value, so postgres knows the type of the value. With a parameterized value however you likely need to give postgres some hints on what type of value this is supposed to be.

5

Okay, figured it out:

Instead of using Jason.encode, use type/2 with :map:

intern = %{"Employment Type" => "Intern"}
insert(:job, custom_fields: intern)

query =
 from j in Job,
 as: :job,
 where: fragment("custom_fields @> ?::jsonb", type(^intern, :map)),
 select: j.id

Thanks!

3 Likes