seymores

seymores

I have a embeds_many field in Postgresql jsonb and I am trying to query it using fragment like this:

 def find(key, val) do
     q = from p in Person,
           where: fragment("meta_tags @> ?", ^"'[{\"#{key}\":\"#{val}\"}]'"),
           select: p
     Repo.all(q)
 end

I don’t understand why hardcoded value in the fragment("meta_tags @> '[{"type":"test"}]'") works but will return no result once I use interpolated values from the function input.

The logs are as below:

Not returning result with interpolated string.

[debug] QUERY OK source="world_persons" db=15.1ms
SELECT w0."id", w0."name", w0."salutation", w0."original_name", w0."gender", w0."dob" w0."email", w0."active", w0."inserted_at", w0."updated_at" FROM "persons" AS w0 WHERE (meta_tags @> $1) ["'[{\"type\":\"test\"}]'"]
[]

With results when I hardcode the value.

[debug] QUERY OK source="world_persons" db=5.2ms decode=0.1ms
SELECT w0."id", w0."name", w0."salutation", w0."original_name", w0."gender", w0."dob", w0."email", w0."active", w0."inserted_at", w0."updated_at" FROM "persons" AS w0 WHERE (meta_tags @> '[{"type":"test"}]') []
(... results)

This won’t work with hardcoded value

where: fragment("meta_tags @> ?", ^"[{\"type\":\"test\"}]"),

This works without the interpolated operator

where: fragment("meta_tags @> ?", "[{\"type\":\"test\"}]"),

From a suggestion in stackoverflow, I cast the query to text and then to jsonb and that works.

where: fragment("meta_tags @> ?::text::jsonb", ^"[{\"#{key}\":\"#{val}\"}]"),

What have I missed in this query to make this work without the casting trick?

Where Next? Top

Trending in Questions Top

RSP87
I’m working on a project that simulates the bumbl example in the programming phoenix book. It acts almost like an email client. We have a...
New
kszambelanczyk
Hello! Could someone please give me a help/sample code, how to delete a file from s3 using waffle/waffle_ecto from Phoenix app. I creat...
New
RemyXRenard
I’m seeing that a list inside a Kino.DataTable will be interpreted as a charlist, even if the Kino.configure() is set to charlists: :as_l...
New
velrest
So my question is quite simple and i have found no conclusive answer on forum, google or AI. Should we use :erlang.float for Integer to ...
New
samoloth
Hi, I’ve just set up an application with ash_authentication. There is only magic link strategy for now, so there is no confirmation add o...
New
FlyingNoodle
If a change or preparation module uses Ash.Changeset.get_argument/2 or Ash.Query.get_argument/2 (or any of the other get_argument functio...
New
ryanwinchester
apply_graft/2 doesn’t rewrite an add_many sub-workflow’s deps on an add step. Grafted jobs cancel with “upstream job was deleted” Version...
New

Other Trending Topics Top

mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
garrison
Hobbes is a low-level distributed database for the Elixir programming language. Hobbes provides a simple, safe, and scalable storage lay...
New
marciok
Hi there! We created Gust: A task orchestrator inspired by Airflow. For those who have never heard about Aiflow, it’s a Python-based wor...
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
Dmk
Xamal is a deployment tool for Elixir apps that deploys native releases to bare metal servers over SSH. It’s a port of GitHub - basecamp/...
New
Damirados
Hello everyone. After busy few months I am happy to announce v0.1.0 of Emerge & Solve. They are GUI (Emerge) and State management (S...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews