json - 从 jsonb 成员更新整数列失败，返回 : column is of type integer but expression is of type jsonb

Question

在 PostgreSQL 9.5 表中，我有一个 integer 列 social。

当我尝试在存储过程中更新它时，在jsonb 类型的 in_users 变量中给定以下 JSON 数据(一个包含 2 个对象的数组，每个对象都有一个“社交”键) :

'[{"sid":"12345284239407942","auth":"ddddc1808197a1161bc22dc307accccc",**"social":3**,"given":"Alexander1","family":"Farber","photo":"https:\/\/graph.facebook.com\/1015428423940942\/picture?type=large","place":"Bochum,
Germany","female":0,"stamp":1450102770},
  {"sid":"54321284239407942","auth":"ddddc1808197a1161bc22dc307abbbbb",**"social":4**,"given":"Alxander2","family":"Farber","photo":null,"place":"Bochum,
Germany","female":0,"stamp":1450102800}]'::jsonb

那么下面的代码是失败的:

    FOR t IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
    LOOP
            UPDATE words_social SET
                    social = t->'social',
            WHERE sid = t->>'sid';
    END LOOP;

错误信息:

ERROR:  column "social" is of type integer but expression is of type jsonb
LINE 3:                         social = t->'social',
                                         ^
HINT:  You will need to rewrite or cast the expression.

我尝试将该行更改为:

social = t->'social'::int,

但是我得到了错误:

ERROR:  invalid input syntax for integer: "social"
LINE 3:                         social = t->'social'::int,
                                            ^

为什么 PostgreSQL 不能识别数据是 integer？

来自JSON-TYPE-MAPPING-TABLE我的印象是 JSON 数字会自动转换为 PostgreSQL 数字类型。

Answer 1

单个基于集合的 SQL 命令比循环更有效:

UPDATE words_social w
SET    social = (iu->>'social')::int
FROM   JSONB_ARRAY_ELEMENTS(in_users) iu  -- in_user = function variable
WHERE  w.sid = iu->>'sid';                -- type of sid?

回答你原来的问题:

Why doesn't PostgreSQL recognize that the data is integer?

因为您试图将 jsonb 值转换为 integer。在您的解决方案中，您已经发现您需要 ->> 运算符而不是 -> 来提取 text，它可以转换为 整数。

您的第二次尝试添加了第二个错误:

t->'social'::int

除上述之外:operator precedence .转换运算符 :: 比 json 运算符 -> 绑定(bind)更强。就像您已经发现自己一样，您真的想要:

(t->>'social')::int

dba.SE 上的情况非常相似:

• Querying JSONB in PostgreSQL