How to Convert Postgresql 9.4's JSONb Type to Float

How to convert PostgreSQL 9.4's jsonb type to float

There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text.

Details: JSON Functions and Operators

Try

SELECT (json_data->'position'->>'lat')::float + 1.0 AS lat
FROM updates
LIMIT 5

Postgres: How to convert a json string to text?

There is no way in PostgreSQL to deconstruct a scalar JSON object. Thus, as you point out,

select  length(to_json('Some "text"'::TEXT) ::TEXT);

is 15,

The trick is to convert the JSON into an array of one JSON element, then extract that element using ->>.

select length( array_to_json(array[to_json('Some "text"'::TEXT)])->>0 );

will return 11.

Postgres: update column type and parse column value from json to float

You can specify a using clause to define the conversion:

alter table example_table
   alter example_column 
      type double precision using (example_column::jsonb ->> 'value')::double precision

Extract an int, string, boolean, etc. as its corresponding PostgreSQL type from JSON

The simplest way for booleans and numbers seems to be to first cast to TEXT and then cast to the appropriate type:

SELECT j::TEXT::NUMERIC
FROM (VALUES ('5.4575e6'::json)) x (j)
-- Result is 5457500, with column type NUMERIC
SELECT j::TEXT::BOOLEAN
FROM (VALUES ('true'::json)) x (j)
-- Result is t, with column type BOOLEAN

This leaves strings, where you instead get back a quoted value trying to this:

SELECT j::TEXT
FROM (VALUES (to_json('foo'::TEXT))) x (j)
-- Result is "foo"

Apparently, that particular part of my question has already been addressed. You can get around it by wrapping the text value in an array and then extracting it:

SELECT array_to_json(array[j])->>0
FROM (VALUES (to_json('foo'::TEXT))) x (j)
-- Result is foo, with column type TEXT.

---

Related Topics