Pg-promise: [Question] Query returns values with JSON

[Fte-github]

Hi !

First of all, thank you for your great plugin !

I'm encountering a special situation and was wondering if pg-promise had a way to work with it. I have a request that returns some "normal" columns, and some "JSON". Example :

ID | Name | Desc
1, 'John', '{"lat":6.2, "long":46.4}'

Then, I encode the full result to a JSON String. But currently the JSON returned by the query is interpreted like a String and its special characters are escaped...

Is there a way to tell to pg-promise that some columns are JSON String and that it can parse it and store a JS Object in the query result ? Or should I continue to parse the JSON myself after getting the query result and before converting everything to JSON ?

I hope that my explanation is understandable...

Thanks in advance for your help !

[demurgos]

What is the type of your "JSON" column in your table schema? Are you using the native JSON Postgres type? Columns using this type are automatically parsed and serialized by pg-promise.

[vitaly-t]

It can be done by making the query via JSON functions. See example with json_build_object here: https://stackoverflow.com/questions/39805736/get-join-table-as-array-of-results-with-postgresql-nodejs

[Fte-github]

Thanks for your answers !

@demurgos Unfortunately I'm not using the "JSON" column type. I'm using the function ST_AsGeoJSON() from PostGIS in my request to convert a geometry binary column to a GeoJSON String.

@vitaly-t I'll look into that, thanks !

[Fte-github]

A huge thank you for your comment @demurgos ! It made me realise that if I cast the result of "ST_AsGeoJSON()" like this : SELECT ST_AsGeoJSON(geom)::json FROM ..., pg-promise understands that it's a JSON String and parse it, exactly like I wanted ! Perfect !