how to avoid pg to parse a result object

[budarin]

I'm using api-service as a proxy between PostgreSQL and client
so JSON.parse result from PostgreSQL and JSON.stringify the result to client is unnecessary and only eat service time.
Could I switch off parsing response from PostgreSQL into JSON?

[charmander]

You can pass a string as a parameter value where JSON is expected. That is,

const obj = JSON.parse(json);
pool.query('some query', [obj])

and

pool.query('some query', [json])

are equivalent when json is a well-formed JSON object.

[budarin]

I'm not speaking about query parameters but about query result

[charmander]

Cast it to text in the query:

SELECT some_json::text FROM somewhere

[budarin]

thanks !

[budarin]

I'm sorry for my pure english
thanks a lot for response!

[mattzeunert]

Is there another way to prevent parsing? I've got a query with an execution time of 12ms, but with ::text on the jsonb field it takes 143ms.

[brianc]

Is there another way to prevent parsing? I've got a query with an execution time of 12ms, but with ::text on the jsonb field it takes 143ms.

how big is the jsonb text in bytes? If you can generate a self contained code sample of this being slow I can take a look, but it can be anything from the throughput between your DB and your app server to the size of the json, etc. If you're returning the results as a string then there's no parsing at all other than the mandatory parsing of turning the raw packets of row data into strings to return.

[mattzeunert]

It's about 5000 rows with about 1kb of JSON each. I didn't run the test with pg, I just ran explain analyze on the queries with and without ::text, and somehow the text conversion did slow it down a lot. (I wonder if it's just because Postgres doesn't normally count the serialization of the row data as part of the execution time?)

I eventually found a way to prevent parsing using the types property:

const types = require("pg").types;
res = await con.query({
  text: `SELECT values FROM table`,
  types: {
    getTypeParser(oid, ...rest) {
      if (oid === types.builtins.JSON || oid === types.builtins.JSONB) {
        return (v) => v;
      }
      return types.getTypeParser(oid, ...rest);
    },
  },
});

[bryanph]

Maybe casting to text using the #>> operator is more performant? (see https://www.postgresql.org/docs/9.4/functions-json.html)