Filtering with JSON Path (Postgres)

Questions & Help Questions
,
4

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.