Skip to main content
4 of 4
added 1 character in body
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

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.

Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496