Hello,
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(:first_name)
field(:last_name)
field(:address, :map)
end
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])})
end
end)
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?
