PostgreSQL – Why Does a JSON Element Return Text When It’s Not Text?

database-internalsdatatypesjsonoperatorpostgresql

When I try to access a JSON element using ->>'elementName' I get a texttype.

SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2)
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'::jsonb)
) AS t(x1,x2);

 pg_typeof | jsonb_typeof 
-----------+--------------
 text      | number
(1 row)

However, jsonb says it maps numbers to numeric types…

When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8-23.

Here is the table reproduced from the docs,

Table 8-23. JSON primitive types and corresponding PostgreSQL types

JSON primitive type     PostgreSQL type  Notes
string                  text             \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8
number                  numeric          NaN and infinity values are disallowed
boolean                 boolean          Only lowercase true and false spellings are accepted
null                    (none)           SQL NULL is a different concept

Best Answer

It isn't currently possible to access the internal JSON types. The quoted documents above mention only how they're stored. There is a pseudo-type in PostgreSQL anyelement, but you can not return that type. Functions are polymorphic in that they accept different types, but they must return a specified type.

The operator could be overloaded for different types, but it isn't currently that way. Currently ->> is defined as

Operator    Right Operand Type  Description
->>         text                Get JSON object field as text

This means no matter how the type is stored, it'll have to go through text to get access to it. All of the jsonb operators return jsonb or text.

Consider the ambiguity even if the type was overloaded, how would this be processed.

SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2)
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'),
  ('{"a":true}'::jsonb, 'true'::jsonb)
) AS t(x1,x2);

If that makes sense.. then what does this do..

SELECT sum(x1->>'a')
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'),
  ('{"a":true}'::jsonb, 'true'::jsonb)
) AS t(x1,x2);

While overloading ->> may make the system more efficient, it would also make it far more complex.

Related Question