Hi, I’m running into problems when trying to interpolate somewhat “simple” json query using a fragment.

I have a jsonb column in postgres that has the following structure (array of objects):

[{"name": "test", ...}, {"name": "another", ...}, ...]

Let’s start with the SQL query that gets me what I need:

SELECT count(*) 
FROM "posts" AS p0 
WHERE (p0."jsonb_column" @> ANY( ARRAY [ '[{"name": "test"}]', '[{"name": "another"}]' ]::jsonb[]));

Translated to ecto (without parametrised variables) this works just fine:

from(p in Post,
        where:
          fragment(
            "? @> ANY (ARRAY['[{\"name\":\"test\"}]','[{\"name\": \"another\"}]']::jsonb[])",
            p.jsonb_column
          )
      )
|> Repo.aggregate(:count)

However when I use parametrised values it ends up generating a slightly different sql query, which obviously doesn’t return the same results. This is the closest I can get it:

vals = [[%{"name" => "test"}], [%{"name" => "another"}]]

from(
        p in Post,
        where:
          fragment(
            "? @> ANY (ARRAY [?]::jsonb[])",
            p.jsonb_column,
            ^vals
          )
      )
|> Repo.aggregate(:count)

This generates the following SQL:

SELECT count(*) FROM "posts" AS p0 WHERE (p0."jsonb_column" @> ANY (ARRAY [$1]::jsonb[]))

with parameters: $1 = '[[{"name": "test"}], [{"name": "another"}]]'
correct parameters would be: '[{"name": "test"}]', '[{"name": "another"}]'

Any ideas about how can I get this to work? Cheers!