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

Questions / Help
, ,
1

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

%Car{id: "123"}

%Person{
  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: c.id == fragment("JSON_EXTRACT(?, \"$.car_id\")" p.options),
    select: {c.id == fragment("JSON_EXTRACT(?, \"$.car_id\")" p.options), fragment("JSON_EXTRACT(?, \"$.car_id\")" p.options), c.id}

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

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

1 Like
2

:wave:

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

Judging by https://stackoverflow.com/questions/39818296/using-mysql-json-field-to-join-on-a-table, you might need to use JSON_UNQUOTE in the join expression like

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

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:

4

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

1 Like