Search code examples
elixirfragmentjsonbecto

How can I query from a joined table with a where condition from a jsonb column using Ecto.Query

I am trying to query the stores of a merchant with a condition

stores = Store
  |> join(:inner, [s], m in Merchant, on: s.merchant_id == m.id)
  |> where([_s, m], fragment("setting->>'test_merchant_enabled'") == "true")
  |> Repo.all()

This is the same command but in sql form

SELECT stores.slug 
FROM stores 
INNER JOIN merchants as m ON stores.merchant_id = m.id 
WHERE m.setting->>'test_merchant_enabled' = 'true'

I know the meaning of the error but I can't figure out how to fix it. ** (Postgrex.Error) ERROR 42702 (ambiguous_column) column reference "setting" is ambiguous

Solution

  • I fixed it by using this

    stores = Store
  |> join(:inner, [s], m in Merchant, on: s.merchant_id == m.id)
  |> where([_s, m], fragment("?.setting->>'test_merchant_enabled'", m) == "true")
  |> Repo.all()