Querying json fields with dynamic document schema

Questions / Help
1

I’m trying to query a json field using fragments.

When the query schema is “known”, everything is fine, but when the json document varies and the client specifies the key path to query, I cannot find the proper way to do it.

Suppose I have a schema with a “details” map field. This map can have any key in it, some key can even point to another map:
>%{“address” => “here”, “additional” => %{“a” => “foo”, “b” => “bar”}}

The static jsonb fragment to filter the query can be something like:
> where: fragment(“details #>> ‘{address}’ LIKE ?”, “%ere%”)

now suppose that the key path is given from the client side, so client asks to query for details.additional, the fragment should be
> where: fragment(“details #>> ‘{additional,a}’ LIKE ?”, “%foo%”)

The question is: how can I dynamically build the string fragment part?

I’ve tried with
> search = build_my_jsonb_q() # something that returns ““details #>> ‘{additional,a}’”
> where: fragment(”? LIKE ?", ^search, ^value)

But obviously this does not work.

My issue is that I don’t know “a priori” the json schema so I cannot create static fragment for every query case.
And is preferred to create a generic query interface in order to “search” into the jsonb field without changing the backend code.

So, what can be the best way (if possible) to do it with ecto ?

1 Like
2

Well, after all this does not seems possible with a single, dynamic fragment (please correct me if I’m wrong).

What I did is to build several fragments and select them based on key path len, something like:

  defp ilike_jsonb_fragment(query, field, key, value) do
    field = String.to_existing_atom(field)
    from q in query, where: fragment("?->>? ILIKE ?", field(q, ^field), ^key, ^value)
  end

  defp ilike_jsonb_fragment(query, field, key1, key2, value) do
    field = String.to_existing_atom(field)
    from q in query, where: fragment("?->?->>? ILIKE ?", field(q, ^field), ^key1, ^key2, ^value)
  end

… and so on

This basically hardcodes the possible key-path len, but well, is acceptable.

Hope this is a good way to deal with it, instead of using raw SQL.

1 Like
3

Another possibility would be:

  defmacrop ilike_jsonb_fragment(query, field, [_ | _] = keys, value) do
    frag =
      keys
      |> Enum.map(fn _ -> "?" end)
      |> Enum.join("->")
      |> case do f -> "#{f}->>?" end
    keys = Enum.map(keys, &quote(do: ^unquote(&1))
    quote do
      from q in query,
        where: fragment(unquote(frag), field(q, ^String.to_existing_atom(field)), unquote_splicing(keys), ^value)
    end
  end

Used like:

query = ilike_jsonb_fragment(query, "some_field", [key1, key2, key3], 42)
4 
defmacro jsonb_fragment(query, data_field, [_ | _] = keys, value) do
    frag =
      keys
      |> Enum.map(fn _ -> "?" end)
      |> Enum.join("->")
      |> case do
        f -> "#{f}->>? = ?"
      end

    keys = Enum.map(keys, &quote(do: ^unquote(&1)))

    quote do
      from q in unquote(query),
        where:
          fragment(
            unquote(frag),
            field(q, ^String.to_existing_atom(unquote(data_field))),
            unquote_splicing(keys),
            unquote(value)
          )
    end
  end

it works

1 Like