Сумма различий по полю массива JSONB в CockroachDB

У меня есть куча объектов jsonb с именем text, которые следуют одной и той же структуре в таблице с именем texts, которая при выборе с помощью следующего запроса выглядит следующим образом:

выберите jsonb_pretty(текст) из лимита текстов 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, которые затем необходимо суммировать с остальными записями в таблице.

Я пробовал кучу разных способов запроса объектов, но я не могу найти ничего, что работает, любая помощь или указатели в правильном направлении будут очень признательны.


json cockroachdb
person NicolaiF    schedule 07.03.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Версия, которая работает с 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)
person Ancoron    schedule 07.03.2019
comment
Выполнение этого запроса, хотя и измененного как: 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›) - person NicolaiF; 08.03.2019
comment
Извините, но это все равно не сработает (даже если ошибки не было) по двум причинам: 1.) jsonb_array_elements возвращает набор (строки) и не может использоваться напрямую в качестве аргументов для агрегатных функций. 2.) Порядок элементов, возвращаемых jsonb_array_elements, не указан, поэтому вы можете в конечном итоге сравнить stop из записи №3 с start из записи №1. В любом случае вы просто удваиваете работу здесь, поэтому я использовал вызов функции LATERAL, возвращающий набор. Кстати. с какой версией PostgreSQL вы работаете? - person Ancoron; 08.03.2019
comment
Он работает на cockroachdb, насколько я понимаю, он должен быть чем-то похож на postgres. - person NicolaiF; 08.03.2019
comment
Хорошо, в этом случае вы должны заменить PostgreSQL на CockroachDB во всех случаях. Да, они многое наследуют от PostgreSQL, но в данном случае не поддерживают LATERAL (см. поддержка SQL для получения подробной информации, вызовы функций, возвращающих набор, в предложении FROM неявно выполняются как LATERAL). - person Ancoron; 08.03.2019
comment
О, я не знал об этом, большое спасибо за хедз-ап - person NicolaiF; 08.03.2019
comment
Кроме того, CockroachDB пока не поддерживает GROUP BY ROLLUP. Я привел еще один пример без этого и без объединения LATERAL. Это может работать для вашей БД. - person Ancoron; 08.03.2019
comment
Что еще хуже, теперь я почти уверен, что cockroachdb не поддерживает WITH: .com/docs/stable/sql-statements.html - person NicolaiF; 08.03.2019
comment
Я просто использовал его, чтобы выделить test_data как временную таблицу. Таким образом, с данными, которые вы имеете, просто замените test_data фактическим SELECT и полностью отбросьте WITH. - person Ancoron; 08.03.2019
comment
Я получаю эту ошибку: pq: jsonb_array_elements(): неподдерживаемый бинарный оператор: ‹jsonb› #› ‹string› (желаемый ‹jsonb›) - person NicolaiF; 08.03.2019
comment
Хм, это поможет, если вы заключите выражение в круглые скобки, например. jsonb_array_elements( (data#>'{0}') )? - person Ancoron; 08.03.2019
comment
По-прежнему выдает ту же ошибку: pq: jsonb_array_elements(): неподдерживаемый бинарный оператор: ‹jsonb› #› ‹string› (желаемый ‹jsonb›) - person NicolaiF; 08.03.2019
comment
Это указывает мне на то, что синтаксический анализатор из CockroachDB здесь немного неполный, поскольку он должен оценивать возвращаемый тип выражения внутри аргумента функции, а не принимать само выражение в качестве типа аргумента, поскольку он должен поддерживать #>. Последняя попытка: data#>'{0}'::text[] - person Ancoron; 08.03.2019
comment
Работает, если вы примените его к списку строк, например: jsonb_array_elements(text::jsonb#›'{0}'::string[]) - person NicolaiF; 08.03.2019
comment
теперь я получаю сообщение об ошибке, что его нельзя преобразовать в десятичное число - person NicolaiF; 08.03.2019
comment
Вау! Здесь дьявол действительно кроется в деталях! ОК, тогда попробуйте с (items.item->>'stop')::decimal (оператор ->> возвращает string, тогда как -> первоначально возвращал jsonb, что может сбить с толку CockroachDB здесь). - person Ancoron; 08.03.2019
comment
Просто из любопытства я сам загрузил и протестировал последнюю версию CockroachDB. Ответ был обновлен, предоставляя рабочий запрос. - person Ancoron; 09.03.2019