用JSONb进行二进制到二进制转换

Question

如何避免不必要的CPU成本？

见这个带有失败测试的历史性问题。示例：j->'x'是一个代表数字的JSONb，j->'y'是一个布尔值。自从JSONb的第一个版本(2014年以9.4发行)到今天(6年！)，与PostgreSQL v12.似乎我们需要执行双重转换：

1. 丢弃j->'x'“二进制JSONb数”信息，并将其转换为可打印字符串j->>'x'； 丢弃j->'y'“二进制JSONb布尔”信息，并将其转换为可打印的高级字符串j->>'y'。
2. 解析字符串，通过转换字符串(j->>'x')::float AS x获得“二进制SQL”； 解析字符串，通过转换字符串(j->>'y')::boolean AS y来获得“二进制SQL”。

程序员是否没有语法或优化的函数来强制直接转换？

我没看到在指南中..。或者它从未得到实施:它是否存在技术障碍？

关于我们需要它的典型场景的说明

(对评论的答复)

假设您的系统需要用最少的磁盘使用量存储许多小数据集(实例化!) ，并使用集中式控制/元数据/等等来管理所有这些数据集。JSONb是一个很好的解决方案，它提供了至少两个很好的选择来存储在数据库中：

1. 元数据(带有模式描述符)和阵列阵列中的所有数据集；
2. 将元数据和表行分隔到两个表中。

​

​

(以及将元数据转换为text[]缓存等的变体)

选择-1，单一，是最好的“最少的磁盘使用”的要求，更快的全面信息检索。Alternative-2可以作为随机访问或部分检索的选择，当表Alt2_DatasetLine也有多个列，比如时间，用于时间序列。

例如，可以在分隔的架构中创建所有SQL视图。

CREATE mydatasets.t1234 AS 
  SELECT (j->>'d')::date AS d,  j->>'t' AS t,  (j->>'b')::boolean AS b,
         (j->>'i')::int AS i,  (j->>'f')::float AS f
  FROM (
   select jsonb_array_elements(j_alldata) j FROM Alt1_AllDataset
   where dataset_id=1234
  ) t
  -- or FROM alt2...
;

并自动创建视图的can，运行SQL字符串动态 .我们可以通过从元数据中提取的简单形成规则来再现上面的“稳定模式转换”：

SELECT string_agg( CASE 
   WHEN x[2]!='text' THEN format(E'(j->>\'%s\')::%s AS %s',x[1],x[2],x[1])
   ELSE  format(E'j->>\'%s\' AS %s',x[1],x[1])
  END, ',' ) as x2
FROM (
 SELECT  regexp_split_to_array(trim(x),'\s+') x
 FROM regexp_split_to_table('d date, t text, b boolean, i int, f float', ',') t1(x)
) t2;

..。这是一个“现实生活场景”，这个(显然丑陋的)模型对于小流量应用来说是惊人的快速。以及其他优点，除了减少磁盘使用:灵活性(您可以更改数据集模式而不需要更改SQL模式)和可伸缩性(同一表中有2、3、……10亿个不同的数据集)。

回到问题:想象一下具有~50或更多列的数据集，如果PostgreSQL提供一个“二合一到二合一的转换”，那么PostgreSQL视图会更快。

Answer 1

简短的回答:不，没有比(例如)更好的提取jsonb数字为PostgreSQL的方法了。

CAST(j ->> 'attr' AS double precision)

JSON号碰巧在内部存储为PostgreSQL numeric，因此无论如何也不会“直接”工作。但是，没有一个主要的原因可以解释为什么没有更有效的方法来提取像numeric这样的值。

那我们为什么不拥有它呢？

1. 没有人实施这一计划。这往往表明，没有人认为值得这样做。我个人认为这将是一种微观优化--如果您想获得最大的效率，可以从JSON中提取该列并将其直接存储在表中。 没有必要修改PostgreSQL源代码来做到这一点。您可以编写自己的C函数，这完全符合您的预期。如果很多人认为这是有益的，我希望有人已经写了这样一个函数。
2. PostgreSQL刚刚完成了实时编译(JIT)。因此，如果对许多行计算这样的表达式，PostgreSQL将动态地为其构建可执行代码。这就缓解了效率低下的问题，并减少了出于效率原因而有特殊情况的必要性。
3. 对于许多数据类型，它可能并不像看起来那么简单。在所有情况下，JSON标准类型并不一定与PostgreSQL类型相对应。这似乎是人为的，但看看黑客邮件列表中的最近的线索，它处理了JSON和PostgreSQL之间的数字类型之间的差异。

所有这些都不是这样一个特性永远不存在的原因，我只是想给出为什么我们没有它的原因。