Subject: change JSON serialization for BIGINT?

From: Tim McLaughlin <[email-masked]>
Date: Tue, 26 Nov 2024 06:34:06 -0500

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

---

From: Victor Yegorov <[email-masked]>
Date: Tue, 26 Nov 2024 14:36:26 +0300

> select row_to_json(row(500::bigint));

Will this work?

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

-- 
Victor Yegorov

---

From: Tim McLaughlin <[email-masked]>
Date: Tue, 26 Nov 2024 08:07:13 -0500

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