Elixir Ecto JSONb Query

Question

I am new to Postgresql JSONb and Ecto. I have a table with a column "configuration" that is jsonb. I am able to insert, but when I try to select from it with where condition using the fragment function I cannot get it to work. Here is Example and output:

iex> Repo.all(from i in Instance, select: i.configuration, where: 
fragment("?->'testing' LIKE '%hey%'", i.configuration))

[debug] QUERY ERROR source="instances" db=0.4ms
SELECT i0."configuration" FROM "instances" AS i0 WHERE 
(i0."configuration"->'testing' LIKE '%hey%') []
** (Postgrex.Error) ERROR 42883 (undefined_function): operator does not 
exist: jsonb ~~ unknown
(ecto) lib/ecto/adapters/sql.ex:431: 
Ecto.Adapters.SQL.execute_and_cache/7
(ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
(ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

If I execute a raw query as such:

iex> query = """
select configuration FROM instances where configuration->>'testing' 
LIKE '%hey%'
"""
iex> Ecto.Adapters.SQL.query!(Repo, query)
[debug] QUERY OK db=1.0ms
select configuration FROM instances where configuration->>'testing' 
LIKE '%hey%' []
%Postgrex.Result{columns: ["configuration"], command: :select,
connection_id: 28581, num_rows: 1, rows: [[%{"testing" => "some test 
hey?"}]]}

It works, likewise in psql the following query works:

select configuration FROM instances where configuration->>'tsting' LIKE '%hey%';

Any help on what I am doing wrong with the Repo.all(... query would be appreciated as I have tried a bunch to no avail and do not understand what I am doing wrong.

mu is too short mu is too short · Accepted Answer · 2017-09-26T03:27:15

Your first query uses the -> operator and that is used to:

Get JSON object field by key

so it gives you jsonb back and the error tells you that there is no ~~ operator (AKA LIKE) that takes a jsonb on the left side.

The query that works uses the ->> operator which returns text and LIKE (AKA ~~) does know what to do with text on the left side.

Elixir Ecto JSONb Query

1 Answers