SQL 如何将 PostgreSQL 9.4 的 jsonb 类型转换为 float
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24826385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert PostgreSQL 9.4's jsonb type to float
提问by fadedbee
I'm trying the following query:
我正在尝试以下查询：
SELECT (json_data->'position'->'lat') + 1.0 AS lat FROM updates LIMIT 5;
(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.)
（+1.0 只是为了强制转换为浮动。我的实际查询要复杂得多，这个查询只是问题的一个测试用例。）
I get the error:
我收到错误：
ERROR: operator does not exist: jsonb + numeric
If I add in explicit casting:
如果我添加显式转换：
SELECT (json_data->'position'->'lat')::float + 1.0 AS lat FROM updates LIMIT 5;
the error becomes:
错误变为：
ERROR: operator does not exist: jsonb + double precesion
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 值不能转换为浮点数，但在这种情况下，我知道 lats 都是 JSON 数字。
Is there a function which casts jsonb values to floats (or return NULLs for the uncastable)?
是否有一个函数可以将 jsonb 值转换为浮点数（或为不可转换的返回 NULL）？
回答by Igor Romanchenko
There are two operations to get value from JSON. The first one ->will return JSON. The second one ->>will return text.
有两种操作可以从中获取值JSON。第一个->将返回JSON。第二个->>将返回文本。
Details: JSON Functions and Operators
详细信息：JSON 函数和运算符
Try
尝试
SELECT (json_data->'position'->>'lat')::float + 1.0 AS lat
FROM updates
LIMIT 5
回答by knitti
AFAIK there's no json->float casting in Postgres, so you could try an explicit (json_data->'position'->'lat')::text::floatcast
AFAIK有Postgres里没有json->浮铸造，所以你可以尝试一个明确的(json_data->'position'->'lat')::text::float投
回答by Erwin Brandstetter
Per documentation, there are also the functions
根据文档，还有功能
jsonb_populate_record()
jsonb_populate_recordset()
Analog to their json twins (present since pg 9.3)
类似于他们的 json 双胞胎（自第 9.3 页起出现）
json_populate_record()
json_populate_recordset()
You need a predefined row type. Either use the row-type of an existing table or define one with CREATE TYPE. Or substitute with a temporary table ad hoc:
您需要一个预定义的行类型。要么使用现有表的行类型，要么使用CREATE TYPE. 或者用临时表临时替换：
CREATE TEMP TABLE x(lat float);Can be a single column or a long list of columns.
可以是单列或一长列列。
Only those columns are filled, where the namematches a keyin the jsonobject. The valueis coerced to the column typeand has to be compatible or an exception is raised. Other keys are ignored.
只有那些中填入，其中名字一个匹配关键的json对象。该值被强制为列类型并且必须兼容或引发异常。其他键被忽略。
SELECT lat + 1 -- no need for 1.0, this is float already
FROM updates u
, jsonb_populate_record(NULL::x, u.json_data->'position')
LIMIT 5;
Using an implicit LATERAL JOINhere.
在这里使用隐式LATERAL JOIN。
Similarly, use jsonb_populate_recordset()to decompose arrays into multiple rows per entry.
类似地，用于jsonb_populate_recordset()将数组分解为每个条目的多行。
This works the same way in Postgres 9.3 with json. There is the added benefit that casting to / from textinternally is not necessary for numeric data in jsonb.
这在 Postgres 9.3 中的工作方式与json. 还有一个额外的好处是，text对于jsonb.
回答by rocksteady
Adding a clarification because this comes up as the top hit for a 'JSONB float conversion' search - note that you need to wrap the JSON conversion in brackets, and thenapply the '::' casting.
添加说明，因为这是“JSONB 浮点转换”搜索的热门搜索 - 请注意，您需要将 JSON 转换括在方括号中，然后应用 '::' 转换。
As mentioned above, the correct method is:
如上所述，正确的方法是：
(json_data #>> '{field}')::float
If instead you try this it will fail:
相反，如果您尝试这样做，它将失败：
json_data #>> '{field}'::float
This was the mistake I was making in my code and it took me a while to see it - easy fix once I noticed.
这是我在代码中犯的错误，我花了一段时间才看到它 - 一旦我注意到就很容易修复。
回答by Luis Castillo
You must to cast the json value to text and then to float.
您必须将 json 值转换为文本然后浮动。
Try this:
尝试这个：
(json_data #>> '{field}')::float
回答by The Code Guy
When creating a view I used CAST:
创建视图时，我使用了 CAST：
create view mydb.myview as
select id,
config->>'version' as version,
config->>'state' as state,
config->>'name' as name,
config->>'internal-name' as internal_name,
config->>'namespace' as namespace,
create_date,
update_date,
CAST(config ->> 'version' as double precision) as version_number
from mydb.mytbl;
