# Filtering with JSON Path (Postgres) **URL:** \ **Category:** Questions\ **Tags:** ash, ash-postgres\ **Created:** [December 27, 2024, 6:48am UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386 "2024-12-27T06:48:54Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![westbaystars](https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/w/58f4c7/32.png) [@westbaystars](https://forum.elixirforum.com/u/westbaystars)\ **Post date:** [December 27, 2024, 6:48am UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386/1 "2024-12-27T06:48:54Z")
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. ```elixir 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. ```elixir filter (expr(fragment("to_json(names)::jsonb @@ '($[*].surname like_regex ? flag \"i\") || ($[*].given_name like_regex ? flag \"i\")'", ^arg(:name), ^arg(:name)))) ``` ```elixir {: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. ---
**Author:** ![westbaystars](https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/w/58f4c7/32.png) [@westbaystars](https://forum.elixirforum.com/u/westbaystars)\ **Post date:** [December 27, 2024, 9:18am UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386/2 "2024-12-27T09:18:19Z")
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. ---
**Author:** ![zachdaniel](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/zachdaniel/32/31980_2.png) [@zachdaniel](https://forum.elixirforum.com/u/zachdaniel)\ **Post date:** [December 27, 2024, 2:16pm UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386/3 "2024-12-27T14:16:57Z")
> [@westbaystars](#): > > ```elixir > filter (expr(fragment("to_json(names)::jsonb @@ '($[*].surname like_regex \"^Maki\" flag \"i\") || ($[*].given_name like_regex \"^Maki\" flag \"i\")'"))) > > ``` 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. ```elixir 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 ``` ---
**Author:** ![westbaystars](https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/w/58f4c7/32.png) [@westbaystars](https://forum.elixirforum.com/u/westbaystars)\ **Post date:** [December 28, 2024, 3:04am UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386/4 "2024-12-28T03:04:04Z")
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: ```elixir 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: ```elixir [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: ```sql 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: ```sql 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: ```sql 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: ```Elixir {: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. ---
**Author:** ![zachdaniel](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/zachdaniel/32/31980_2.png) [@zachdaniel](https://forum.elixirforum.com/u/zachdaniel)\ **Post date:** [December 28, 2024, 3:38am UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386/5 "2024-12-28T03:38:32Z")
> [@westbaystars](#): > > ```elixir > 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 > > ``` How about ```elixir 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`? ---
**Author:** ![westbaystars](https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/w/58f4c7/32.png) [@westbaystars](https://forum.elixirforum.com/u/westbaystars)\ **Post date:** [December 28, 2024, 5:08am UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386/6 "2024-12-28T05:08:34Z")
> [@zachdaniel](#): > > ```elixir > 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 > > ``` 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. ---
**Author:** ![westbaystars](https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/w/58f4c7/32.png) [@westbaystars](https://forum.elixirforum.com/u/westbaystars)\ **Post date:** [December 29, 2024, 8:29am UTC](https://forum.elixirforum.com/t/filtering-with-json-path-postgres/68386/7 "2024-12-29T08:29:57Z")
This was the clue that I needed to get it to work. I just moved the query building to the Postgres side. ```sql 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`: ```elixir 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`, `or`ing that with calling it with `:aliases` worked as expected. A query through the `Sports` domain yields: ```elixir 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.