Postgresql (Aurora) Сумма из jsonb дает нечетную ошибку

Я пытаюсь суммировать значения из столбца типа jsonb в таблице в базе данных Aurora/Postgres, но, похоже, это не работает.

select (payload->>'loanAmount')::int from rfqs limit 1;

Дает результаты 10000 (int4).

select sum((payload->>'loanAmount')::int) from rfqs limit 1;

Дает результат: ERROR: invalid input syntax for integer: "2000.5"

Похоже, это как-то связано с тем, как оператор ->> преобразует json в строку, но похоже, что что-то не так с этой строкой, что не позволяет правильно преобразовать ее в тип int.

В качестве теста я сделал select SUM(('10000'::int));, который работал нормально и вернул 10000, как и ожидалось.

Любые идеи?


postgresql casting amazon-aurora jsonb
person Matt    schedule 12.06.2018    source источник
comment
приведите его как float вместо int, т.е. замените ::int на ::float   -  person Haleemur Ali    schedule 12.06.2018
comment
Приведение к типу с плавающей запятой устраняет ошибку, но дает неверный результат... например, если я ограничусь двумя строками, равными 10000 и 12000, итоговое число с плавающей запятой будет равно 9180615,5.   -  person Matt    schedule 14.06.2018
comment
Вы понимаете, что ограничение 1 в вашем select sum(...) из rfqs не имеет смысла? Вы выбираете сумму всех строк, независимо от того, указываете ли вы ограничение 1, ограничение 10 или вообще без ограничения.   -  person Gerard H. Pille    schedule 14.06.2018
comment
@Matt, я думаю, вам было бы полезно узнать больше о том, как работают агрегатные функции и группировка по. limit 1 не будет ограничивать только 1 строку в сумме, сумма будет применяться ко всем строкам, а затем будет применено ограничение, поскольку сумма является редуктором, вы всегда будете получать не более 1 строки на группу. поскольку группы не указаны, существует только 1 группа.   -  person Haleemur Ali    schedule 14.06.2018
comment
Удаление оператора limit не меняет того, что приведение к целому числу по-прежнему дает ту же ошибку, а приведение к типу с плавающей запятой дает неверное значение. Кажется, это отлично работает в sqlfiddle, но не в Aurora, поэтому мне интересно, не является ли это странной проблемой Aurora?   -  person Matt    schedule 14.06.2018
comment
Это потому, что 2000.5 не является целым числом. Целые числа не могут иметь дробные цифры.   -  person a_horse_with_no_name    schedule 07.11.2018

Ответы (2)


arrow_upward
0
arrow_downward

Это позволит вам понять (вы увидите, в чем проблема с "::int")

select sum(payload->>'loanAmount') from rfqs

что то же самое, что:

select sum(payload->>'loanAmount') from rfqs limit 1

(Агрегат без группировки по возвращает только по строке, поэтому «предел 1» немного излишен)

Пытаться

SELECT sum(to_number((payload->>'loanAmount'),'999999999D9999')) from rfqs

см. http://www.sqlfiddle.com/#!17/9c30a/8

person Gerard H. Pille    schedule 12.06.2018
comment
Неважно, сколько строк я выберу... Я выбрал 1 просто для простоты. То же самое происходит, если я выбираю 2 строки или 9000. - person Matt; 14.06.2018
comment
Конечно, в этом смысл лишнего. Что вы получаете от выбранной суммы (payload-››'loanAmount) из запроса запросов? - person Gerard H. Pille; 14.06.2018
comment
оператор -›› в столбце jsonb возвращает строку, поэтому ее нужно каким-то образом привести к типу... но приведение к целому числу дает ошибку, указанную в исходном сообщении, а приведение к типу с плавающей запятой дает странное огромное число, которое не t вообще фактическая сумма рассматриваемых значений. - person Matt; 14.06.2018
comment
Одно из значений creditAmount должно содержать дробную часть или слишком велико для целого числа. - person Gerard H. Pille; 14.06.2018
comment
Ошибка, кажется, указывает на это, но, насколько я могу видеть, это не так, и приведение как bigint или float по-прежнему не работает. Я предполагаю, что есть что-то странное в том, как -›› создает строку, что означает, что это не совсем корректная строка... но я еще не нашел способа выяснить, что с ней не так. - person Matt; 14.06.2018
comment
выберите (payload-››'loanAmount') из запросов, где payload-››'loanAmount' например %2000.5% ? - person Gerard H. Pille; 14.06.2018
comment
Запросы, которые я изначально указал, а также запрос в вашем sqlfiddle, отлично работают на sqlfiddle, но не работают с экземпляром Aurora/Postgres. Мне интересно, есть ли проблема с Aurora по сравнению с обычным Postgres. - person Matt; 14.06.2018
comment
Давайте продолжим обсуждение в чате. - person Gerard H. Pille; 14.06.2018

arrow_upward
0
arrow_downward

Некоторые из ваших свойств "loanAmount" не имеют целочисленного значения. Хотя первая запись да.

Чтобы найти записи о правонарушениях:

SELECT payload FROM rfqs WHERE (payload->>'loanAmount') <> trunc(payload->>'loanAmount')
person user482745    schedule 07.11.2018