Thread: change JSON serialization for BIGINT?

Is there a way to have Postgres serialize BIGINT as a string rather than number in JSON?  By default it does this:


select row_to_json(row(500::bigint));
 row_to_json 
-------------
 {"f1":500}

But I want it to do this (note that "500" is quoted):

select row_to_json(row(500::bigint));
 row_to_json 
-------------
 {"f1":"500"}

I tried doing this, but it has no effect:

CREATE or replace FUNCTION bigintJson(bigint) RETURNS json
    AS 'select $1::text::json;'
    LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

create cast (bigint as json) with function bigintJson(bigint) as implicit;

Thanks for any guidance.

--
Tim McLaughlin

вт, 26 нояб. 2024 г. в 14:34, Tim McLaughlin <tim@gotab.io>:

Will this work?

select row_to_json(row(500::text));

Thanks for the idea.  We could do that, but that would require code changes to our thousands of lines of SQL that generate JSON to add the explicit cast to text.  I am hoping not to have to do that.

There are numerous places where I would think this would be useful:
BIGINT or NUMERIC overflows Javascript Number Max Integer (my problem) with the default JSON parser in NodeJS
DECIMAL / NUMERIC value loses precision due to being ingest as a JSON Number which is annotated in high precision using a standard JSON parser

There are probably others, but the point is that there are cases where it would be great to override the default JSON serialization of Postgres scalars rather than have to go explicitly cast them all to text first.

--
Tim McLaughlin