How to Correctly Convert JSONB to Float in PostgreSQL?

JSONB to Float Conversion in PostgreSQL 9.4

A PostgreSQL user attempted to convert a jsonb column to a float using the following query:

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

However, they encountered the error:

ERROR:  operator does not exist: jsonb + numeric

To resolve this issue, the user attempted explicit casting using the ::float operator, resulting in the error:

ERROR:  operator does not exist: jsonb + double precesion

To convert jsonb values to floats, it's essential to follow the correct operator precedence. The -> operator returns a JSON value, while the ->> operator returns a text value. To successfully cast to float, you need to use the ->> operator.

The correct query syntax is:

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

By using the ->> operator, the jsonb value is first converted to text, which can then be cast to float using the ::float operator.

The above is the detailed content of How to Correctly Convert JSONB to Float in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!