Ecto fragment question

Hi, there!

I am having a hard time using fragment to query MySQL JSON field

from([channel] in query, where: fragment("metadata->>'$.?' = ?", ^key, ^value))

metadata is my JSON field. When I run this code I get MySQL syntax error.

If I hard-code JSON key

from([channel] in query, where: fragment("metadata->>'$.test0' = ?", ^value))

It works but since this is free form JSON I have no prior knowledge of what keys there are inside JSON.

If I do this

from([channel] in query, where: fragment("metadata->>'$.#{key}' = ?", ^value))

I get compilation errors.

Any ideas on what might be a proper solution to this?

In prepared statements, ? is not used for interpolation, but to replace an entire parameter. So you can’t use it inside a string.

I found an (ugly) way to use a placeholder as a json key:
JSON_UNQUOTE(JSON_EXTRACT(metadata, CONCAT('$.', ?))) = ?

As placeholders doesn’t seem to work on operators such as ->>, ->, ||, …, I just used their function counterparts.

Awesome! I did look into those functions but CONCAT trick did not occur to me. Thank you very much for your help. It works!

With Ecto v3.4 you should be able to do simply:

where: channel.metadata[^key] == ^val
6 Likes

That is very cool. I also see that there is support for indexing into an array. Is there syntax for querying something like table.column.key[?any?].blah == ^val. Specifically, the ?any? bit.

This is not supported but I think it would be interesting. It’s unclear how it would look like, perhaps: channel.metadata[Access.all()]? Not sure.

If you’d like to discuss this, a good starting point is Consider adding u.settings["field"] for reading JSON fields · Issue #3216 · elixir-ecto/ecto · GitHub (and the linked PRs) where you can find additional context.