Ecto dynamic select_merge with string keys from jsonb?

Questions / Help
,
1

I have a jsonb field which has many different possible fields inside it.

I am trying to use Ecto to make a dynamic query.

Suppose I have an “items” table with a “fields” jsonb field.
Suppose the json stored in the “fields” jsonb field looks like this:

{ 
     "a_field_name": "some value", 
     "another_name": "another value", 
     "some_name": "my value"
}

Each record may have different json field names.

I am trying to implement a method which will dynamically query only the fields that are specified as an Elixir list.

Eg. input could be ["a_field_name", "some_name"]

Desired output would be something like:

[ 
    %{ 
        "a_field_name" => "some value", 
        "some_name" => "my_value"
    }
]

Is this possible? The keys can’t be atoms because there is no upper bound on possible different json keys.

Currently, the only ways I know are maybe to

  • construct SQL query manually
  • or use fixed atoms such as :field_1, :field_2 … and then map them back … but that seems to be a waste of processor time
2

I know how to construct select_merge calls, but as noted, it doesn’t seem want to take string keys.

Below is what I’m trying to get to work:

query = "items" |> select(%{})
query_fields = ["a_field_name", "some_name"]

query = query_fields |> Enum.reduce(query, fn field, acc ->
        acc |> select_merge(%{^field => fragment("fields->?", ^field) })
    end)

EDIT: hmmm wait, that actually works.

EDIT2: It works only when there is one member in query_fields!

If query_fields is ["a_field_name", "some_name"]
then the result query will look like:

#Ecto.Query<from i0 in "items",
 select: %{^"a_field_name" => fragment("fields->?", ^"a_field_name")}>

The second field will be missing and the result when running the query will be

** (ArgumentError) parameters must be of length 2 for query %Postgrex.Query
3

I believe this has been recently fixed in Ecto master.

3 Likes