How to construct a jsonb containing a number?



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


  • 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

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e60de76843bd07277dcbe651ab835411




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2