Cast all columns of numeric type to text when converting a row to json-CodePudding

I have table with numeric/decimal columns and I am converting the rows to json

select to_jsonb(t.*) from my_table t

I need to have the numeric columns casted to text before converted to json.

The reason why I need this is JavaScript don't handle really big numbers well so I may loose a precision. I use decimal.js and the string representation is best to construct the decimal.js number from.

I know I can do this

select to_jsonb(t.*) || jsonb_build_object('numeric_column', numeric_column::text) from my_table t

But I want to have it done automatically. Is there a way to somehow cast all numeric columns to text before passing to to_jsonb function?

It can be user-defined postgres function.

EDIT: Just to clarify my question. What I need is some function similar to to_jsonb except all columns of the type numeric/decimal are stored as string in the resulting JSON.

Thanks

CodePudding user response：

You can run a query like:

select row_to_json(row(t.column1,t.column2,t.column_numeric::text)) from my_table t

Result here

CodePudding user response：

This solution converts all the json values into text :

SELECT jsonb_object_agg(d.key, d.value)
  FROM my_table AS t
 CROSS JOIN LATERAL jsonb_each_text(to_jsonb(t.*)) AS d
 GROUP BY t

whereas this solution only converts json numbers into text :

 SELECT jsonb_object_agg(d.key, CASE WHEN jsonb_typeof(d.value) = 'number' THEN to_jsonb(d.value :: text) ELSE d.value END)
  FROM my_table AS t
 CROSS JOIN LATERAL jsonb_each(to_jsonb(t.*)) AS d
 GROUP BY t

test result in dbfiddle.