How to compare a fragment with a query reference variable in ecto?

So, let’s say I have a model Car, and a model Person, with a structures:

%Car{id: "123"}

  options: "{"car_id":"123"}"

options being a text field on the underlying DB.
To fetch that from the database I can use a fragment like:
"JSON_EXTRACT(?, \"$.car_id\")"

It works when using the order_by statement for example, however, when fetching all Persons and joining all the related Cars based on that fragment, it doesn’t…

I tried this, and put that return value on the select statement to try to debug:

 from p in Person, 
    join: c in Car, on: == fragment("JSON_EXTRACT(?, \"$.car_id\")" p.options),
    select: { == fragment("JSON_EXTRACT(?, \"$.car_id\")" p.options), fragment("JSON_EXTRACT(?, \"$.car_id\")" p.options),}

which basically returns [{false, "123", "123"}] (false on every comparison)

I would be grateful if somebody had a hint for this.

1 Like


Are you using MySQL? I couldn’t find JSON_EXTRACT function in Postgres …

Judging by, you might need to use JSON_UNQUOTE in the join expression like

from p in Person, 
    join: c in Car, on: == fragment("JSON_UNQUOTE(JSON_EXTRACT(?, \"$.car_id\"))" p.options),
    select: { == fragment("JSON_UNQUOTE(JSON_EXTRACT(?, \"$.car_id\"))" p.options), fragment("JSON_EXTRACT(?, \"$.car_id\")" p.options),}
1 Like

Yes, I’m using MySQL. Forgot to say but I’m using JSON_UNQUOTE as well, it removes the extra quotes, but the result is still the same :frowning:

using UNHEX(JSON_UNQUOTE(JSON_EXTRACT(...))) solved the problem.

1 Like