代码之家 › 专栏 › 技术社区 › NicolaiF

PostgreSQL中JSONB数组字段上的差异之和

jsonb postgresql database

NicolaiF · 技术社区 · 7 年前

我有一堆名为文本在一个名为短信当使用以下查询选择时,如下所示:

从文本限制1中选择jsonb_pretty(文本);

[                                                                                                                                                                                                                                                                                                                                                                   
        [                                                                                                                                                                                                                                                                                                                                                               
            {                                                                                                                                                                                                                                                                                                                                                           
                "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对象的所有差异(stop-start)。

这样,第一个对象的结果是:(2.25-0.02)+(291.88-291.45)+(293.63-292.07)=4.22,然后需要与表中的其余条目相加。

我尝试了很多不同的方法来查询对象,但我找不到任何有效的方法,如果能提供任何帮助或指向正确的方向,我将不胜感激。

0 回复 | 直到 7 年前

Ancoron 7 年前

适用于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)