How to construct a jsonb containing a number?

by

The question:

Is there a way to construct a jsonb with only a numeric value in it?

For example,

SELECT pg_typeof(('{"a":1}'::jsonb) -> 'a');

indicates that ('{"a":1}'::jsonb) -> 'a' has the jsonb type and it only contains a numeric value 1.

But how do I create a jsonb with 1 in it directly without constructing and destructing an object?

Direct type casting does not seem to work:

# SELECT 1::jsonb;
ERROR:  cannot cast type integer to jsonb
LINE 1: SELECT 1::jsonb;

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

You need to cast from a string, not from a number: '1'::jsonb. This is because using '1' means that the result is actually a constant, whereas 1 is a runtime conversion.

to_jsonb(1) also works, but I think this uses a runtime conversion.

SELECT pg_typeof('1'::jsonb), jsonb_typeof('1'::jsonb);
pg_typeof jsonb_typeof
jsonb number

db<>fiddle


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment