How Can I Correctly Convert PostgreSQL's jsonb to Float for Arithmetic Operations?

Converting PostgreSQL's jsonb Type to Float

This article addresses the challenge of converting PostgreSQL's jsonb type to float. The difficulty arises when attempting to perform arithmetic operations on jsonb data, which by default returns an error.

Initial Query and Errors

An initial query is presented:

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

This query triggers an error because the operator cannot be applied to jsonb and numeric types. Adding explicit casting, as shown below, yields another error:

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

The error message now indicates that the operator cannot be used between jsonb and double precision.

JSON Operators

The issue stems from the fact that the -> operator returns a JSON object, while ->-> outputs text. In order to cast the jsonb value to float correctly, the ->-> operator should be used.

Solution

The solution to this problem is to replace the -> operator with ->->:

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

This modified query now correctly casts the jsonb value to float and performs the addition operation without errors.

The above is the detailed content of How Can I Correctly Convert PostgreSQL's jsonb to Float for Arithmetic Operations?. For more information, please follow other related articles on the PHP Chinese website!