Filtering with JSON Path (Postgres)

I just realized that I have a :name argument and am calculating the :name calculation field (which is the name for the given :lang). Changing the argument to be :search_name (with appropriate arg(...) updates makes no difference.

The primary issue here is that the whole value after @@ is a single string. The fragment logic is replacing ? with the parameter replacements, but postgres doesn’t accept parameters just inside of a string like that.

Try replacing your filter with this preparation. Note that I’ve also used the expression syntax to interpolate the names reference. You should do this instead of putting in names literally as that can cause ambiguous queries.

prepare fn query, _ -> 
  jsonpath = '($[*].surname like_regex \"^#{query.arguments.name}\" flag \"i\") || ($[*].given_name like_regex \"^#{query.arguments.name}\" flag \"i\")'

  Ash.Query.filter(query, fragment("to_json(?)::jsonb @@ ?", names, jsonpath)
end

Ah, I see. The problem becomes the PSQL side being unable to interpolate the parameters within the '...'.

After putting double quotes around the value being assigned to jsonpath, there was progress.

Revising the prepare statement like so:

      prepare fn query, _context ->
        jsonpath = "'($[*].surname like_regex \"^#{query.arguments.search_name}\" flag \"i\") || ($[*].given_name like_regex \"^#{query.arguments.search_name}\" flag \"i\")'"

        Ash.Query.filter(query, fragment("to_json(names)::jsonb @@ ?", ^jsonpath))
      end

I now get the error:

[error] Postgrex.Protocol (#PID<0.381.0>) disconnected: ** (Postgrex.QueryError) type `jsonpath` can not be handled by the types module Postgrex.DefaultTypes
[debug] QUERY ERROR source="persons" db=0.0ms queue=18.5ms idle=1463.0ms
SELECT p0."id", p0."names" FROM "persons" AS p0 WHERE ((to_json(names)::jsonb @@ $1)) ["'($[*].surname like_regex \"^Maki\" flag \"i\") || ($[*].given_name like_regex \"^Maki\" flag \"i\")'"]

Postgres is now complaining about the \, expecting them to be followed by a command. They were necessary to escape the quotes on the Elixir side, but appear to now be passed on as-is to the PSQL side.

Switching to the PSQL side, I prepared a named statement as:

prepare person_search (unknown) as
  select id, names from persons WHERE ((to_json(names)::jsonb @@ $1));

I wasn’t sure about what type to use to define the @@ portion, so I used unknown and it figured it out. text did not work.

And with some experimentation, got it to run with:

execute person_search('($[*].surname like_regex "^Maki" flag "i") || ($[*].given_name like_regex "^Maki" flag "i")');
  id   |                                                                                                                                                                                                                 names
-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1170 | {"{\"lang\": \"en\", \"surname\": \"Makita\", \"full_name\": \"Kazuhisa Makita\", \"given_name\": \"Kazuhisa\"}","{\"lang\": \"ja\", \"surname\": \"牧田\", \"full_name\": \"牧田 和久\", \"given_name\": \"和久\", \"surname_ruby\": \"まきた\", \"full_name_ruby\": \"
まきた かずひさ\", \"given_name_ruby\": \"かずひさ\"}","{\"lang\": \"zh_tw\", \"surname\": \"牧田\", \"full_name\": \"牧田 和久\", \"given_name\": \"和久\"}"}
  2049 | {"{\"lang\": \"en\", \"surname\": \"Makihara\", \"full_name\": \"Taisei Makihara\", \"given_name\": \"Taisei\"}","{\"lang\": \"ja\", \"surname\": \"牧原\", \"full_name\": \"牧原 大成\", \"given_name\": \"大成\", \"surname_ruby\": \"まきはら\", \"full_name_ruby\": \"まきはら たいせい\", \"given_name_ruby\": \"たいせい\"}"}
...

With that, modifying the generated SELECT statement to remove the surrounding double quotes and backslashes, I get:

SELECT p0."id", p0."names" FROM "persons" AS p0 WHERE ((to_json(names)::jsonb @@ $1)) ['($[*].surname like_regex "^Maki" flag "i") || ($[*].given_name like_regex "^Maki" flag "i")'];
ERROR:  there is no parameter $1
行 1: ... "persons" AS p0 WHERE ((to_json(names)::jsonb @@ $1)) ['($[...

The error returned to the Elixir side is:

{:error,
 %Ash.Error.Unknown{
   bread_crumbs: ["Error returned from: Myapp.Sports.Person.search_with_name"], 
   query: "#Query<>", 
   errors: [
     %Ash.Error.Unknown.UnknownError{
       error: "** (Postgrex.QueryError) type `jsonpath` can not be handled by the types module Postgrex.DefaultTypes",
       field: nil,
       value: nil,
       splode: Ash.Error,
       bread_crumbs: ["Error returned from: ScoutDragon.Sports.Person.search_with_name"],
       vars: [],
       path: [],
       stacktrace: #Splode.Stacktrace<>,
       class: :unknown
     }
   ]
 }}

I’ve been unable to find the SELECT {statement} [{params}] syntax in the Postgres documentation. I trust that this creates a temporary prepared statement and executes it. I’ve tried a number of combinations to wrap the parameters (in quotes, back-tics, etc.) with no luck.

I feel like this is so very close.

How about

prepare fn query, _context ->
        jsonpath = "'($[*].surname like_regex \"^#{query.arguments.search_name}\" flag \"i\") || ($[*].given_name like_regex \"^#{query.arguments.search_name}\" flag \"i\")'"

        Ash.Query.filter(query, fragment("to_json(names)::jsonb @@ ?::unknown", ^jsonpath))
      end

?
Or perhaps ?::text::unknown?

Thank you for the prompt response. However, that also didn’t work.

I’m now looking into how to create a function on the PSQL side that will do do it.

Thank you for your time.

Take care.

This was the clue that I needed to get it to work. I just moved the query building to the Postgres side.

create or replace function person_name_search(person_names jsonb, name text)
  returns boolean as $$
  declare
    name text = coalesce(nullif(trim(name), ''), 'a');
    jsonpath jsonpath = concat(
      '($[*].surname like_regex "^', name, '" flag "i") || ',
      '($[*].given_name like_regex "^', name, '" flag "i")');
  begin
    return person_names @@ jsonpath;
  end; $$
  language plpgsql;

I added a check to make sure that the passed parameter isn’t NULL or empty, defaulting to searching for all names that begin with a. I’m doing that on the Elixir side as well, so it should never be NULL, but it seems like a good idea to verify either way.

Then I call the person_name_search function in a filter with fragment:

    read :search_with_name do
      prepare build(load: [name: expr(%{lang: ^arg(:lang)})])
      filter (expr(
        fragment("person_name_search(to_json(names)::jsonb, ?)", ^arg(:search_name))
        ||
        fragment("person_name_search(to_json(aliases)::jsonb, ?)", ^arg(:search_name))
      ))

      argument :search_name, :string do
        allow_nil? false
        default "a"
      end

      argument :lang, Lang do
        allow_nil? false
        default :en
      end
    end

I believe that the search_name parameter is still being made safe before passing it on. Please correct me if someone thinks not.

This is expanded from my original question to include searching the :aliases field, which is also an array of PersonName embeds. Once I had this working for :names, oring that with calling it with :aliases worked as expected.

A query through the Sports domain yields:

Sports.search_persons("牧", :en)

{ok: [
   #Myapp.Sports.Person<
     name: #Myapp.Sports.PersonName<
       __meta__: #Ecto.Schema.Metadata<:built, "">,
       lang: :en,
       given_name: #Ash.CiString<"Haruki">,
       middle_names: nil,
       surname: #Ash.CiString<"Makino">,
       full_name: #Ash.CiString<"Haruki Makino">,
       registered_name: nil,
       given_name_ruby: nil,
       middle_names_ruby: nil,
       surname_ruby: nil,
       full_name_ruby: nil,
       registered_name_ruby: nil,
       aggregates: %{},
       calculations: %{},
       ...
     >,
     ...
     id: 47326,
     sid: "makino-haruki",
     ...
   >,
   #Myapp.Sports.Person<
     name: #Myapp.Sports.PersonName<
       __meta__: #Ecto.Schema.Metadata<:built, "">,
       lang: :en,
       given_name: #Ash.CiString<"Shoya">,
       middle_names: nil,
       surname: #Ash.CiString<"Makino">,
       full_name: #Ash.CiString<"Shoya Makino">,
       registered_name: nil,
       given_name_ruby: nil,
       middle_names_ruby: nil,
       surname_ruby: nil,
       full_name_ruby: nil,
       registered_name_ruby: nil,
       aggregates: %{},
       calculations: %{},
       ...
     >,
     ...
     id: 47353,
     sid: "makino-shoya",
     ...
   >,
   ...
 ]}

Thank you again for the hint to build the jsonpath portion separately. That did it.

Take care.