public inbox for [email protected]  

help / color / mirror / Atom feed

change JSON serialization for BIGINT?
3+ messages / 2 participants
[nested] [flat]

* change JSON serialization for BIGINT?
@ 2024-11-26 11:34  Tim McLaughlin <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Tim McLaughlin @ 2024-11-26 11:34 UTC (permalink / raw)
  To: [email protected]

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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: change JSON serialization for BIGINT?
@ 2024-11-26 11:36  Victor Yegorov <[email protected]>
  parent: Tim McLaughlin <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Victor Yegorov @ 2024-11-26 11:36 UTC (permalink / raw)
  To: Tim McLaughlin <[email protected]>; +Cc: [email protected]

вт, 26 нояб. 2024 г. в 14:34, Tim McLaughlin <[email protected]>:

> 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"}
>

Will this work?

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

-- 
Victor Yegorov


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: change JSON serialization for BIGINT?
@ 2024-11-26 13:07  Tim McLaughlin <[email protected]>
  parent: Victor Yegorov <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: Tim McLaughlin @ 2024-11-26 13:07 UTC (permalink / raw)
  To: Victor Yegorov <[email protected]>; +Cc: [email protected]

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
On Nov 26, 2024 at 6:36 AM -0500, Victor Yegorov <[email protected]>, wrote:
> вт, 26 нояб. 2024 г. в 14:34, Tim McLaughlin <[email protected]>:
> > > 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"}
>
> Will this work?
>
> select row_to_json(row(500::text));
>
> --
> Victor Yegorov


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2024-11-26 13:07 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-26 11:34 change JSON serialization for BIGINT? Tim McLaughlin <[email protected]>
2024-11-26 11:36 ` Victor Yegorov <[email protected]>
2024-11-26 13:07   ` Tim McLaughlin <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox