Posgrex, How to get every row as a map?

Questions / Help
1

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”},

Greetings

2 Likes
2

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?

2 Likes
3

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.

3 Likes
4

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
5

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
6

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.

3 Likes