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

AFAIK Postgres 中没有 json->float 强制转换，因此您可以尝试显式强制转换(json_data->'position'->'lat')::text::float

有两个操作可以从中获取值。第一个将返回 .第二个将返回文本。JSON->JSON->>

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

尝试

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

每个文档，还有函数

jsonb_populate_record()
jsonb_populate_recordset()

模拟他们的 json 双胞胎（从第 9.3 页开始存在）

json_populate_record()
json_populate_recordset()

您需要预定义的行类型。使用现有表的行类型或使用 定义一个行类型。或者用临时临时表代替：CREATE TYPE

CREATE TEMP TABLE x(lat float);

可以是单个列，也可以是一长串列。

仅填充这些列，其中名称与对象中的键匹配。该值被强制转换为列类型，并且必须兼容，否则会引发异常。其他键将被忽略。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 中的工作方式相同。还有一个额外的好处，即对于 中的数字数据，不需要在内部强制转换。jsontextjsonb

您必须将 json 值转换为文本，然后转换为浮点数。

试试这个：

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

添加一个说明，因为这是“JSONB 浮点转换”搜索的热门话题 - 请注意，您需要将 JSON 转换括在括号中，然后应用“：：”转换。

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

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

相反，如果您尝试这样做，它将失败：

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

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

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

现在我们可以做到了！

如今，我们可以直接从 JSONb 转换为 SQL 数据类型。我正在使用 PostgreSQL v12.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”换算，哪里有微优化呢？
  例如，什么是更快的（？） tham “binary JSONb → string → binary SQL”？boolean→boolean， number→numeric， number→int， number→bigint;number→flloat，number→double。

• 为什么不针对 NULL 进行优化？
  实际上，“NULL 到 SqlType”不起作用，“错误：无法将 jsonb null 转换为整数类型”。

---

基准测试建议

如何检查？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错误？

即使是现在，2021 年的 pg13 版本......不强制转换 NULL 没有意义：自然是强制转换为整数，但 PostgreSQL 在自动强制转换中失败：NULL::int

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

导致“错误：无法将 jsonb null 转换为整数类型”。