如何将PostgreSQL 9.4的jsonb类型转换为float [英] How to convert PostgreSQL 9.4's jsonb type to float
问题描述
我正在尝试以下查询:
SELECT (json_data->'position'->'lat') + 1.0 AS lat FROM updates LIMIT 5;
(+1.0只是强制转换为浮点型)我的实际查询要复杂得多,这个查询只是一个测试用例的问题。)
(The +1.0 is just there to force conversion to float. My actual queries are far more complex, this query is just a test case for the problem.)
我得到错误:
ERROR: operator does not exist: jsonb + numeric
投放:
SELECT (json_data->'position'->'lat')::float + 1.0 AS lat FROM updates LIMIT 5;
错误变为:
ERROR: operator does not exist: jsonb + double precesion
大多数jsonb值不能转换为浮动,但在这种情况下,我知道lats都是JSON数字。
I understand that most jsonb values cannot be cast into floats, but in this case I know that the lats are all JSON numbers.
有一个函数,将jsonb值转换为浮点 $
Is there a function which casts jsonb values to floats (or return NULLs for the uncastable)?
推荐答案
有两个操作可以从 JSON
。第一个 - >
将返回 JSON
。第二个 - >>
将返回文本。
There are two operations to get value from JSON
. The first one ->
will return JSON
. The second one ->>
will return text.
详细信息: JSON函数和运算符
尝试
SELECT (json_data->'position'->>'lat')::float + 1.0 AS lat
FROM updates
LIMIT 5
这篇关于如何将PostgreSQL 9.4的jsonb类型转换为float的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!