Posgrex, How to get every row as a map?

Hi I am moving a web app that work with geospatial data, from express to cowboy.

I have a many sql selects that run without problems using node pg-promise,
for example:

sql= `select json_build_object(
              'id', farms.id::text,
              'name', farms.name,                
              'centroid', (SELECT st_AsGeoJSON(st_centroid(ST_Extent(boundary)))
                        FROM fields where farms.id = fields.farmid )::json       
            as farm FROM farms where userid = 5 ORDER BY name`;

In the result every row is an object:

rows:[{"farm":{"id":"6","name":"La Candelaria","centroid":{"type":"Point","coordinates":[-63.710060569,-31.327962513]}}},
      {"farm":{"id":"8","name":"New farm 10","centroid":{"type":"Point","coordinates":[-63.844279209,-31.339159407]}}},
      {"farm":{"id":"5","name":"San Teodoro","centroid":{"type":"Point","coordinates":[-63.700476101,-31.395135145]}}}]

But with Postgrex every row have inside a list, and also, lost the object name “farm”.

rows: [
    %{"id" => "6", "name" => "La Candelaria", "centroid" => %{"coordinates" => [-63.710060569, -31.327962513],"type" => "Point" }}
    %{"id" => "8", "name" => "New farm 10", "centroid" => %{ "coordinates" => [-63.844279209, -31.339159407], "type" => "Point"}}
    %{"id" => "5", "name" => "San Teodor", "centroid" => %{"coordinates" => [-63.700476101, -31.395135145], "type" => "Point" }}

How to configure Posgrex to give me a list of maps

rows: [%{"farm" => %{....},%{"farm" => %{....},%{"farm" => %{....}]

The other alternative is reprocess the rows:

newRows = Enum.map(rows, fn [row] -> %{"farm" => row} end)

But, there are a lot of select and some are very complex.

The version from mix.exs are: {:postgrex, “~> 0.16”} and {:jason, “~> 1.3”},



It’s hard without looking at your current code, but I figure something like this might help you to visualize:

from q in query, select: %{"farm" => map(q, [:field1, :field2]}

I believe this would return a list of maps with the key “farm” and the given values.
Could you also share your schema and a sample of the data?


Postgrex will just return whatever the SQL returns. It isn’t a query builder or result transformer. If you want to have a specific structure returned you’ll need to write the SQL to return it.

Alternatively you can use Ecto which does provide some more query manipulation tools.


Sorry, it isn’t. Postgres itself will return rows with a single column named farm. pg-promise is formatting the pg.Results that it gets from Node’s equivalent of Postgrex into that shape.

You could do the same transformation in Elixir:

result = ... do something to get a Postgrex.Result ...

Enum.map(result.rows, fn row -> Enum.zip(result.columns, row))

to get the [%{"farm" => %{....},%{"farm" => %{....},%{"farm" => %{....}] shape you’re expecting.

1 Like

How previously say in the OP, I can get my desired output if I do:

newRows = Enum.map(rows, fn [row] -> %{"farm" => row} end)

I thought maybe postgrex had the ability to configure the output,
For example in python psycopg2 you can configure the output with:
cursor = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)

In node pg-promise is not necessary, is straightforward.

1 Like

It does not, it just returns relatively low level results, and then you can take those simple elixir values and do any transformation on them that you require. For a query / result management DSL, see Ecto.