Filtering with JSON Path (Postgres)

Questions & Help Questions
,
7

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.