Обновление целочисленного столбца из члена jsonb завершается неудачно с помощью: column имеет тип integer, но выражение имеет тип jsonb

Question

В таблице PostgreSQL 9.5 у меня есть integer колонка social,

Когда я пытаюсь обновить его в хранимой процедуре, учитывая следующие данные JSON (массив с 2 объектами, каждый из которых имеет "социальный" ключ) в in_users переменная типа jsonb:

'[{"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?

Чтобы ответить на ваш оригинальный вопрос:

Почему PostgreSQL не распознает, что данные целочисленные?

Потому что вы пытались преобразовать jsonb значение для integer, В своем решении вы уже обнаружили, что вам нужно ->> оператор вместо -> извлекать text, который может быть приведен к integer,

Ваша вторая попытка добавила вторую ошибку:

t->'social'::int

В дополнение к вышесказанному: приоритет оператора. Оператор броска :: связывает сильнее, чем оператор JSON ->, Как вы уже нашли себя, вы действительно хотите:

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

Очень похожий случай на dba.SE:

• Запрос JSONB в PostgreSQL

Answer 2

Я закончил тем, что использовал:

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

        IF NOT FOUND THEN
                INSERT INTO words_social (social)
                VALUES ((t->>'social')::int);
        END IF;
END LOOP;