Porting a multi-lingual site from using a native XML database to Postgres with JSONB fields, I’m running into problems when I want to filter by a value in the JSON array.
defmodule Myapp.Sports.Person do
use Ash.Resource,
domain: Myapp.Sports,
data_layer: AshPostgres.DataLayer
require Ash.Expr
alias Myapp.Sports.{..., PersonName, ...}
alias Myapp.Calculations.LangEmbeds
...
actions do
...
read :search_with_name do
prepare build(load: [name: expr(%{lang: ^arg(:lang)})])
filter (expr(fragment("to_json(names)::jsonb @@ '($[*].surname like_regex \"^Maki\" flag \"i\") || ($[*].given_name like_regex \"^Maki\" flag \"i\")'")))
argument :name, :string do
allow_nil? false
end
argument :lang, Lang do
allow_nil? false
default :en
end
end
end
...
attributes do
...
attribute :names, {:array, PersonName}, public?: true, allow_nil?: false
...
end
The above read action works in finding all persons with either surname or given_name beginning with “Maki” in the array of PersonName
. However, when I change the \"^Maki\"
to a ?
and pass in ^arg(:name)
as both parameters, they don’t appear to be getting inserted into the final query.
filter (expr(fragment("to_json(names)::jsonb @@ '($[*].surname like_regex ? flag \"i\") || ($[*].given_name like_regex ? flag \"i\")'", ^arg(:name), ^arg(:name))))
{:error,
%Ash.Error.Unknown{
bread_crumbs: ["Error returned from: Myapp.Sports.Person.search_with_name"],
query: "#Query<>",
errors: [
%Ash.Error.Unknown.UnknownError{
error: "** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near \"$1\" of jsonpath input\n\n query: SELECT p0.\"attributes\", p0.\"id\", p0.\"links\", p0.\"aliases\", p0.\"names\", p0.\"updated_at\", p0.\"inserted_at\", p0.\"sid\", p0.\"xid\", p0.\"bridges\", p0.\"date_of_birth\", p0.\"date_of_death\", p0.\"drafts\", p0.\"gender\", p0.\"location_of_birth_id\", p0.\"location_of_death_id\", p0.\"stints\" FROM \"persons\" AS p0 WHERE ((to_json(names)::jsonb @@ '($[*].surname like_regex $1 flag \"i\") || ($[*].given_name like_regex $2 flag \"i\")'))",
field: nil,
value: nil,
splode: Ash.Error,
bread_crumbs: ["Error returned from: MyApp.Sports.Person.search_with_name"],
vars: [],
path: [],
stacktrace: #Splode.Stacktrace<>,
class: :unknown
}
]
}}
I’ve tried a number of variants of this approach, but none seem to pass the parameters to the fragment
.
Any hints would be greatly appreciated.