Ecto jsonb array escaping

Questions / Help
1

I’m getting strange character escaping when using an array of jsonb objects in Postgres with Ecto:

field :data, {:array, :map}, default: []
data = [%{"foo" => "bar"}]
Repo.insert(%MySchema{data: data})

If I then inspect the database with psql, the data has been escaped:
{"{\"foo\": \"bar\"}"}

I’m not getting any escaping for an ordinary jsonb field. Is this expected?

1 Like
2

That’s due to how psql prints arrays. The data is still correct in the database.

2 Likes
3

I’m suspecting the same thing here in my Postgres queries, I checked the log, unfortunately, a simple query like, isn’t working:

  def search_by_tags(query, tag) do
    tags_array_value = "'[\"#{tag}\"]'"

    from p in query,
      where: fragment("? @> ?", p.tags, ^tags_array_value)
  end
4

I am facing a silimar query and I do not know how to make it work. Did you find a solution?
This is how I am using it:

where: fragment(“not ? @> ‘[?]’”, c.exclusions, ^dish_id)

And this ia the sql that is generated:

(not o0.“exclusions” @> ‘[$2]’)

It does not run:

** (Postgrex.Error) ERROR 22P02 (invalid_text_representation): invalid input syntax for type json

If I run plain sql it works though:

AND (not o0.“exclusions” @> ‘[83055]’)

I can not figure it out how to reproduce this query via Ecto fragment.

1 Like
5

Instead of this, what happens if you do:

where: fragment("not ? @> ?", c.exclusions, ^[dish_id])