CODEWITHSUNDEEP
postgresqljsonb
Peter Krauss
Peter Krauss

Reputation: 13920

How to cast a JSON value to its correspondent datatype?

JSON-string is SQL-text, JSON-number is SQL-numeric, JSON-boolean is SQL-boolean... So I can do "real good cast"... But:

SELECT to_jsonb('hello'::text)::text

is not good. Returning with quotes. Worst case:

SELECT to_jsonb(1::int)::int;
-- ERROR:  cannot cast type jsonb to integer

So, how to do casting?

Upvotes: 1

Views: 4145

Answers (2)

Peter Krauss
Peter Krauss

Reputation: 13920

  ((this is a Wiki, you can edit!))

(update for) Modern PostgreSQL do direct cast!

Example using pg 13.5:

SELECT (('{"x":123}'::jsonb)->'x')::int; -- ok no error!
SELECT (('{"x":123.44}'::jsonb)->'x')::float; -- ok no error!
SELECT (('{"x":123.44}'::jsonb)->'x')::numeric; -- ok no error!
SELECT (('{"x":"hello !"}'::jsonb)->'x')::text; -- no error, but quotes
SELECT ('{"x":"hello !"}'::jsonb)->>'x'; -- ok!

-- Modern versions not need cast for simple operations:

SELECT (('{"x":123}'::jsonb)->'x')::int + 3;     -- Ok, but...
SELECT jsonb_path_query('{"x":123}', '$.x + 3'); -- How about this!

OLD answers for pg 9.4

Ugly ways only...

Today PostgreSQL is not so serious about JSON... So, let's workaround.

Sorry there are questions (mine!) and answers. The answer with the most simple solution is this one:

In pg v9.4.4+ using the #>> operator works for me: select to_json('test'::text)#>>'{}';

So, using the question's example:

SELECT (to_json(1::int)#>>'{}')::int;  -- the cast works

JSONb question... Internal binary value was reused?

SELECT (to_jsonB(10.7::numeric)#>>'{}')::numeric; -- internal repres. preserved? 
SELECT (to_jsonB(10.7::float)#>>'{}')::float; -- internal repres. preserved? 
 -- internal JSONb was float or numeric?
SELECT (to_jsonB(true)#>>'{}')::boolean;  -- internal representation preserved?

... there are no guarantee that PostgreSQL's internal parser is doing the correct thing, reusing internal numerical representation of JSONb to avoid CPU-time consuption (converting jsonb-number to SQL-text, them casting text to SQL-number).

"JSONb cast optimization" or "SQL datatypes/JSONb datatypes convertion optionmization" seems to still be a big gap for PostgreSQL.

Upvotes: 1

Mike Kinney
Mike Kinney

Reputation: 200

Here's a simpler solution that I've found for PostgreSQL.

Assuming:

{
"additionaldata" :
   {
      "Duration" : "123.44"
   }
}

The following select works:

select
  (additionaldata -> 'Duration')::jsonb::text::numeric as durationSeconds
from ...

Upvotes: 0

Related Questions