How to construct a jsonb containing a number?

All we need is an easy explanation of the problem, so here it is.

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;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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);

db<>fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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