Сумма различий по полю массива JSONB в CockroachDB
У меня есть куча объектов jsonb с именем текст, которые следуют одной и той же структуре в таблице с именем тексты, которая при выборе с помощью следующего запроса выглядит следующим образом:
select jsonb_pretty(text) from texts limit 1;
[
[
{
"speaker": null,
"start": 0.02,
"stop": 2.25,
"text": "random text 123"
},
{
"speaker": null,
"start": 291.45,
"stop": 291.88,
"text": "random text 123"
},
{
"speaker": null,
"start": 292.07,
"stop": 293.63,
"text": "random text 123"
}
],
[]
]
Я хотел бы суммировать все различия (стоп-старт) в объектах jsonb таблицы.
таким образом, что первый объект дает: (2,25-0,02)+(291,88-291,45)+(293,63-292,07) = 4,22, которые затем необходимо суммировать с остальными записями в таблице.
Я пробовал кучу разных способов запроса объектов, но я не могу найти ничего, что работает, любая помощь или указатели в правильном направлении будут очень признательны.
Ответы 1
Версия, которая работает с CockroachDB (проверено с v2.1.5):
CREATE TABLE test_data (
id serial primary key,
data jsonb not null
);
INSERT INTO test_data (data) VALUES
('[[{"speaker":null,"start":0.02,"stop":2.25,"text":"random text 123"},{"speaker":null,"start":291.45,"stop":291.88,"text":"random text 123"},{"speaker":null,"start":292.07,"stop":293.63,"text":"random text 123"}],[]]'::jsonb),
('[[{"speaker":null,"start":0.05,"stop":1.97,"text":"random text 123"},{"speaker":null,"start":260.78,"stop":261.23,"text":"random text 123"},{"speaker":null,"start":272.07,"stop":273.73,"text":"random text 123"}],[]]'::jsonb)
;
SELECT test_data.id,
sum((items.item->>'stop')::numeric - (items.item->>'start')::numeric)
FROM test_data
INNER JOIN (SELECT id, jsonb_array_elements(data#>'{0}'::string[]) AS item FROM test_data) AS items ON (items.id = test_data.id)
GROUP BY 1
ORDER BY 1;
... в результате:
id | sum
+--------------------+------+
432708358512836609 | 4.22
432708358512869377 | 4.03
(2 rows)
Объяснение:
tree | field | description
+---------------------------------+-------------+-------------------+
sort | |
│ | order | +id
└── group | |
│ | aggregate 0 | id
│ | aggregate 1 | sum(column6)
│ | group by | @2
└── render | |
└── join | |
│ | type | inner
│ | equality | (id) = (id)
├── project set | |
│ └── scan | |
│ | table | test_data@primary
│ | spans | ALL
└── scan | |
| table | test_data@primary
| spans | ALL
(17 rows)
При полной поддержке PostgreSQL вы можете использовать соединения LATERAL и GROUP BY ROLLUP, чтобы получить общую сумму:
SELECT id, sum((items->'stop')::numeric - (items->'start')::numeric)
FROM test_data, LATERAL jsonb_array_elements(data#>'{0}') AS items
GROUP BY rollup(1)
ORDER BY 1 NULLS LAST;
...результат:
id | sum
----+------
1 | 4.22
2 | 4.03
| 8.25
(3 rows)
Объяснение (на самом деле проще, чем в CockroachDB из-за отсутствия соединения и сортировки по индексу):
QUERY PLAN
-------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.16..4815.59 rows=1271 width=36)
Group Key: test_data.id
Group Key: ()
-> Nested Loop (cost=0.16..2577.21 rows=127000 width=36)
-> Index Scan using test_data_pkey on test_data (cost=0.15..37.20 rows=1270 width=36)
-> Function Scan on jsonb_array_elements items (cost=0.01..1.00 rows=100 width=32)
(6 rows)
Извините, но это все равно не сработает (даже если ошибки не было) по двум причинам: 1.) jsonb_array_elements возвращает набор (строки) и не может использоваться напрямую в качестве аргументов для агрегатных функций. 2.) Порядок элементов, возвращаемых jsonb_array_elements, не указан, поэтому вы можете в конечном итоге сравнить stop из записи № 3 с start из записи № 1. В любом случае вы просто удваиваете работу здесь, поэтому я использовал вызов функции LATERAL set-return. Кстати. с какой версией PostgreSQL вы работаете?
Он работает на cockroachdb, насколько я понимаю, он должен быть чем-то похож на postgres.
Хорошо, в этом случае вы должны заменить PostgreSQL на CockroachDB во всех случаях. Да, они многое наследуют от PostgreSQL, но в данном случае они не поддерживают LATERAL (подробности см. в Поддержка SQL, вызовы функций, возвращающих наборы, в предложении FROM неявно выполняются как LATERAL).
О, я не знал об этом, большое спасибо за хедз-ап
Кроме того, CockroachDB пока не поддерживает GROUP BY ROLLUP. Я привел еще один пример без этого и без соединения LATERAL. Это может работать для вашей БД.
Что еще хуже, теперь я почти уверен, что cockroachdb не поддерживает WITH: cockroachlabs.com/docs/stable/sql-statements.html
Я просто использовал его, чтобы выделить test_data как временную таблицу. Таким образом, с данными, которые вы имеете, просто замените test_data фактическим SELECT и полностью отбросьте WITH.
Я получаю эту ошибку: pq: jsonb_array_elements(): неподдерживаемый бинарный оператор: <jsonb> #> <string> (желаемый <jsonb>)
Хм, это поможет, если вы заключите выражение в круглые скобки, например. jsonb_array_elements( (data#>'{0}') )?
По-прежнему выдает ту же ошибку: pq: jsonb_array_elements(): неподдерживаемый бинарный оператор: <jsonb> #> <string> (желаемый <jsonb>)
Это указывает мне на то, что синтаксический анализатор из CockroachDB здесь немного неполный, поскольку он должен оценивать возвращаемый тип выражения внутри аргумента функции, а не принимать само выражение в качестве типа аргумента, поскольку он должен поддерживать операцию #>. Последняя попытка: data#>'{0}'::text[]
Работает, если вы примените его к списку строк, например: jsonb_array_elements(text::jsonb#>'{0}'::string[])
теперь я получаю сообщение об ошибке, что его нельзя преобразовать в десятичное число
Вау! Дьявол действительно кроется здесь в деталях! Хорошо, тогда попробуйте с (items.item->>'stop')::decimal (оператор ->> возвращает string, тогда как -> изначально вернул jsonb, что здесь может сбить с толку CockroachDB).
Просто из любопытства я сам загрузил и протестировал последнюю версию CockroachDB. Ответ был обновлен, предоставляя рабочий запрос.
Выполнение этого запроса, хотя и измененного как: SELECT id, sum((jsonb_array_elements(text#>'{0}')->'stop')::numeric - (jsonb_array_elements(text#>'{0}')->' start')::numeric) FROM texts GROUP BY rollup(id); выдает следующую ошибку: pq: jsonb_array_elements(): неподдерживаемый бинарный оператор: <jsonb> #> <string> (требуемый <jsonb>)