如何将PostgreSQL 9.4的jsonb类型转换为浮点数

Question

我正在尝试以下查询：

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

(+1.0只是用来强制转换为浮点数。我的实际查询要复杂得多，这个查询只是问题的一个测试用例。)

我收到错误消息：

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值无法转换为浮点数，但在这种情况下我知道所有的纬度都是JSON数字。

是否有一个函数可以将jsonb值转换为浮点数（或者对于不能转换的返回NULL）？

- fadedbee

---

Postgres 默认没有 try_cast 函数。你需要自己编写它们。 - Ihor Romanchenko

相关问题：如何将Postgres json(b)转换为整数？，转换为文本？，转换为布尔值？ - Bergi

Answer 1

从JSON中获取值有两种方法。第一种方法->会返回JSON，而第二种方法->>则会返回文本。

详细信息请参见：JSON函数和运算符

尝试一下

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

- Ihor Romanchenko

---

可能你不想改变数值，所以使用 ::float + 0.0 AS lat，1个纬度的变化是非常大的差异！！ - Abhijit Gujar

为什么我需要添加0.0（或在您的情况下为1.0），它不是已经使用::float转换为浮点数了吗？ - n3rd

请在第12.3页上按照 https://dev59.com/zGAf5IYBdhLWcg3wSQ8z#62621483 所示进行“直接转换”更正。 - Peter Krauss

无缺陷地访问内部元素。 - Gaurav

Answer 2

据我所知，在Postgres中没有针对JSON到float的转换，因此您可以尝试使用明确的(json_data->'position'->'lat')::text::float类型转换。

- knitti

---

在pg12.3+上，您可以执行“直接转换”，如https://dev59.com/zGAf5IYBdhLWcg3wSQ8z#62621483所示。 - Peter Krauss

Answer 3

现在我们可以做到！

如今，我们可以直接将JSONb转换为SQL数据类型。我正在使用PostgreSQLv12.3，它可以很好地工作：

SELECT (j->'i')::int, (j->>'i')::int, (j->'f')::float, (j->>'f')::float
FROM  (SELECT '{"i":123,"f":12.34}'::jsonb) t(j); 

---

子问题：

• 从哪个版本开始可以实现？

• 这是语法糖还是真正的转换？

• 如果是真正的“二进制JSONb→二进制SQL”转换，那么微优化在哪里？
  例如，什么比“二进制JSONb→字符串→二进制SQL”更快？boolean→boolean、number→numeric、number→int、number→bigint；number→float、number→double。

• 为什么不针对NULL进行优化？
  有趣的是，“NULL到SqlType”的转换无法正常工作，“ERROR: cannot cast jsonb null to type integer”。

---

基准测试建议

如何检查？PostgreSQL何时优化循环查询？

EXPLAIN ANALYSE SELECT (j->'i')::int, (j->'f')::float       -- bynary to bynary INT and FLOAT
-- EXPLAIN ANALYSE SELECT (j->>'i')::int, (j->>'f')::float  -- string to bynary INT and FLOAT

-- EXPLAIN ANALYSE SELECT (j->'i')::numeric, (j->'f')::numeric    -- bynary to bynary NUMERIC
-- EXPLAIN ANALYSE SELECT (j->>'i')::numeric, (j->>'f')::numeric  -- string to bynary NUMERIC

FROM (
  SELECT (('{"i":'||x||',"f":'||x||'.34}')::jsonb) as j FROM  generate_series(1,599999) g(x)
  -- SELECT (('{"i":123,"f":12.34}')::jsonb) as j FROM  generate_series(1,599999) g(x)
) t;

PostgreSQL的bug？

即使到了2021年，使用pg13版本，不将NULL强制转换为整数就没有意义：自然而然的，应该将NULL::int转换为整数，但是PostgreSQL在自动转换时失败了：

SELECT (j->'i')::int FROM  (SELECT '{"i":null}'::jsonb) t(j); -- fail

结果显示"ERROR: 无法将jsonb null转换为整数类型"。

- Peter Krauss

---

似乎唯一的优化方法是使用jsonb_populate_record（还可以检查jsonb_to_record或jsonb_to_recordset）。另请参阅此dba讨论。 - Peter Krauss

“无法将jsonb null转换为整数类型”不是一个错误。您也不能将对象或数组转换为整数。请使用NULLIF(j->'i', 'null')::int或(j->>'i')::int。 - Bergi

@Bergi，我使用另一种观点。似乎是规范错误（而不是实现错误），并且过时了。将项目进行转换，例如x[1]::int是原子性的，而不是转换复杂对象。select NULL::int;不是错误，因为编译器必须友好：所以，select (j->'i')::int也必须友好...我们处于2023年，ChatGPT的时代，而不是编译器的石器时代：必须友好。 - Peter Krauss

Answer 4

根据文档, 还有这些函数

jsonb_populate_record()
jsonb_populate_recordset()

类似于它们的 json 双胞胎（自 pg 9.3 起存在）

json_populate_record()
json_populate_recordset()

你需要一个预定义的行类型。可以使用现有表的行类型或使用 CREATE TYPE 定义一个新的行类型。或者可以临时替换为临时表：

CREATE TEMP TABLE x(lat <b>float</b>);

可以是单列或列的长列表。

只有那些名称与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;

在这里使用隐式 LATERAL JOIN。

同样，使用jsonb_populate_recordset()将数组分解为每个条目的多行。

在Postgres 9.3中，这与json的工作方式相同。另外一个好处是，在jsonb中，对于数字数据，内部不需要进行到/从text的转换。

- Erwin Brandstetter

---

JSONb的优点在于其数字和布尔二进制格式，无需中间转换为文本。因此，这个答案是最好的，因为要记住一个好习惯：不要浪费CPU时间进行中间CAST到文本！另一方面，不幸的是，PostgreSQL开发人员只做了这些：关于PostgreSQL中“非冗余CAST”的唯一证据是使用jsonb_populate_record（或记录集）时。请参见https://dba.stackexchange.com/a/271249/90651。 - Peter Krauss

Answer 5

因为这篇文章成为“JSONB浮点数转换”的搜索结果排名最高，我想添加一些说明 - 注意你需要将JSON转换用括号括起来，然后再使用'::'操作符进行类型转换。

如上所述，正确的方法是：

(json_data #>> '{field}')::float

如果你尝试以下操作，它将失败：

json_data #>> '{field}'::float

这是在我的代码中犯的错误，花了我一段时间才发现 - 一旦注意到就很容易修复。

- rocksteady

---

在pg12.3+上，您可以执行“直接转换”，如https://dev59.com/zGAf5IYBdhLWcg3wSQ8z#62621483所示（下面） - Peter Krauss

Answer 6

你需要将json值转换为文本，然后再转换为浮点数。

尝试这样做：

(json_data #>> '{field}')::float

- Luis Castillo

Answer 7

创建视图时，我使用了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;

- The Code Guy