-2

Is there some way for ST_AsGeoJSON to return a dictionary instead of a json-string? I want to use this value in another API, which leads to the string being escaped like this instead of being used as a json object (which would make more sense):

{
  "id": 1234, 
  "name": "Somestreet", 
  "geom": "{\"type\":\"MultiLineString\",\"crs\":{\"type\":\"name\",\"properties\":{\"name\":\"EPSG:3857\"}},\"coordinates\":[[[1738990.19,6133244.29],[1739205.08,6133275.45]]]}"
}

whereas the desired output should be 

{
  "id": 1234, 
  "name": "Somestreet", 
  "geom": {
    "type":"MultiLineString",
    "crs":{"type":"name","properties":{"name":"EPSG:3857"}},
    "coordinates":[[[1738990.19,6133244.29],[1739205.08,6133275.45]]]
  }
}

Of course I could deserialize this object first into a dict, and put it in my response, but that seems like a lot of pointless performance waste.

Improve this question
6
  • 2
    The JSONB funtcions allow for exact constructuion of objects, but it's more involved. The double double-quoting, however, is likely a serialisation issue of the receiving application. – geozelot May 5 '20 at 9:54
  • @geozelot nope, the result of ST_AsGeoJSON is a JSON-string. If you put that into an object it of course gets escaped. That's the whole point of the post, how to not get a string out of ST_AsGeoJSON or similar function, but a dictionary. – FrankyBoy May 5 '20 at 11:46
  • 2
    ST_AsGeoJSON returns plain text, as defined per PGs TEXT data type. PG has no notion of dictionaries, and if plain text is interpreted as JSON-string or object is primarily a question of serialization (e.g. psycopg2 is happily serializing those TEXT values into valid JSON...albeit probably with quite a bit of regexp effort). However, you can cast the result to PGs JSONB type and see where that get's you, i.e. ST_AsGeoJSON(<geometry>, <precision>)::JSONB. – geozelot May 5 '20 at 12:17
  • 1
    Why not. Or maybe even better, file a PR yourself; returning JSONB instead of TEXT should be a few lines of code, but needs a separate function signature. ...however, I bet on the argument that a cast is even less involved than that. – geozelot May 5 '20 at 12:33
  • 1
    It does, you just need to cast as jsonb, which avoids all the \" escape nastiness. If you send the output of ST_AsGeoJSON(geom)::jsonb with json.loads then you will end up with a python dictionary. – John Powell May 5 '20 at 12:41
2

Not sure how you are interacting with database, but if you're using psycopg2 then you can do following.

If you're using cursor.fetchall() with following setting:

 conn = psycopg2.connect(host=app.config['DB_HOST'], port=app.config['DB_PORT'], database=app.config['DB_NAME'],                                          user=app.config['DB_USER'], password=app.config['DB_PWD'])
 cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
 query = "SELECT ST_AsGeoJSON(geom) AS geom FROM table1"
 result = cur.execute(query)

 # The following command will print geometry dict
 print(result['geom'])

it will return associative array (i.e. dictionary).

Or you can use internal object of SQL alchemy as explained here

Improve this answer
3
  • Unless this is some magic of the RealDictCursor, cannot. confirm. The result of ST_AsGeoJSON is a string, not a dict. SELECT id, ST_AsGeoJSON(geom) as geom FROM table1 --> i.imgur.com/iM9Vhqs.png – FrankyBoy May 5 '20 at 11:51
  • 2
    Why would you expect ST_AsGeoJSON to return a dictionary? What Sasa says is correct. – John Powell May 5 '20 at 12:44
  • @JohnPowell I would expect it actually returns a json data type, not a text. Json data types are generally transformed into objects/dicts by your ORM. – FrankyBoy May 6 '20 at 16:32
2

@geozelot mentioned the actual proper solution in a comment, so I'll pull it out into a separate post to mark it as solved.

ST_AsGeoJSON returns plain text, as defined per PGs TEXT data type. PG has no notion of dictionaries, and if plain text is interpreted as JSON-string or object is primarily a question of serialization (e.g. psycopg2 is happily serializing those TEXT values into valid JSON...albeit probably with quite a bit of regexp effort). However, you can cast the result to PGs JSONB type and see where that get's you, i.e. ST_AsGeoJSON(, )::JSONB

So, yeah, the solution is quite simply to cast to JSONB, which means the returned data type is actually "json", and not "text" (at which point the ORM takes over).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.