Ecto fragment jsonb[] interpolation

Your Libraries & OS Mentoring Libraries
,
1

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!

2

I don’t think ARRAY is going to do what you want - ARRAY[$1] means an array with exactly one element taken from the bind parameters.

What might work better is passing the list serialized as a SQL array of JSON:

$1 = '{"{\"name\": \"test\"}", "{\"name\": \"another\"}"}'

Then the consuming code looks like ? @> ANY(?::jsonb[])

You may need to supply additional type information for vals to make this work.

3

Thanks @al2o3cr, I tried this approach too by serializing values individually within a list, however it still doesn’t produce the correct results. Not sure if I’m doing it correctly though…

vals = [[%{"name" => "test"}], [%{"name" => "another"}]]
serialized = vals |> Enum.map(fn x -> x |> Jason.encode!() end)

with fragment being:

fragment("? @> ANY(?::jsonb[])",
    p.services,
    ^serialized
)

I don’t think this is necessary, because the raw sql query works as expected without it, it’s just I can’t get ecto to do the same thing :face_with_head_bandage: