denis

denis

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!

Showing Posts 1 to 2

al2o3cr

al2o3cr

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.

denis

denis OP

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:

— All posts loaded —

Where Next? Top

Trending in Announcing Top

restlessronin
The repo is at GitHub - cyberchitta/openai_ex: Community maintained Elixir library for OpenAI API · GitHub. Docs are at OpenaiEx User Gu...
152 12366 136
New
type1fool
WebAuthnLiveComponent WebAuthnComponents See this post about renaming the package. Passwordless authentication for Phoenix LiveView app...
New
mudasobwa
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
ausimian
Emily is an Elixir library that runs Nx computations on Apple’s MLX. Install it as the default Nx backend and Nx, defn, Axon, Nx.Serving,...
New
kip
In 2021 I started a new library called Tempo with the objective of modelling time as a set of intervals - not as instants. In 2022 I gave...
New
quatermain
Hello, I’m sharing my plugin here in forum after some time so it has time to mature and proof yourself. I use Claude Code daily on a pr...
New

Other Trending Topics Top

KristerV
Hey. Is there anyone here who creates agents in their apps? Not talking about using agents, but creating them. I’m finding it pretty diff...
New
juhalehtonen
There has been a thread to discuss the Stack Overflow Developer Survey on this forum every year since 2018, so here’s yet another one for...
New
webofbits
With AI doing more of the implementation work, I’ve been wondering how much coding I should deliberately keep doing myself. My main conc...
#ai
New
budgie
I love Elixir. It’s one of 2 programming languages I’ve ever fallen in love with. But I don’t use it anymore. Serverless was the promis...
New
OndrejValenta
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly) AI dream has many faces, but general...
New
alexslade
Fly’s CEO posted this recently - Turn And Face The Strange · The Fly Blog It says that Fly is going all-in on sprites, which is a worry ...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews