Select map with dynamic custom keys in ecto query

I have an app where I’d like users to select a table to query from and fields to select and return. Some of my tables have json fields and the user can specify these as well. For example given the following schema,

schema "users" do
  field(:address, :map)

a request to query it will look like this {"source" => "users", "fields" => ["first_name", "address:city"]} where "address:city" is a reference to a json field address, and a json path city, separated by :, and first_name is just a normal field.
With this sample request, I would like to return the data as a map with string keys like;

%{"first_name" => "Jon", "address:city" => "NY"}.

If the fields and source were known at compile time, I would simply hard code as;

|> select([u], %{"first_name" => u.first_name, "address:city" => fragment("?#>?", u.address, ["city"])})

I could also use select_merge in a reduce loop like below - but I can’t for reasons related to other parts of the larger query;

Enum.reduce(query, fields, fn field_name, accum_query ->
  case String.split(field_name, ":") do
     # normal field with no json path
     [fld | []] ->  
       # safe to do this as the atoms are loaded in another part of the code
       fld = String.to_existing_atom(fld)
       select_merge(accum_query, [u], %{^field_name => field(u, ^fld)})
     # field with json path
     [fld, json_path | []] -> 
        fld = String.to_existing_atom(fld)
        select_merge(accum_query, [u], %{^field_name => fragment("?#>?", field(u, ^fld), [^json_path])})

That leaves me with one option - use json_object like select(query, [u], fragment("json_object(?)", ["first_name", u.first_name, "address:city", fragment("?#>?", u.address, ["city"])])). But because the list of fields is dynamic, I need to write a macro that can generate the list of key/value items, and I’m having trouble with this. Any ideas?

Even macros cannot make runtime values compile time available, so this won‘t work. But recent ecto versions introduced native json access support, so you shouldn‘t need fragments anymore.

1 Like

@LostKobrakai Even with the json_extract_path function, how is it possible to build the string-keyed map dynamically?

Hi all,
I was unable to crack this so I ended up using a different approach with select_merge