如何對使用正則表達式過濾的 json 列的值求和？

用正則表達式過濾的json列的值如何求和？

列：

A.name:帶有名稱產品的 Varchar

B.products:時間活躍的 JSON 按產品

詢問：

select
   A.name as product,
   sum(((REGEXP_MATCH(B.products->>'status', 'Active:.(.*?)\"'))[1])::float) as metric
from
   tbl_accounts A
inner join tbl_products B on
   A.identifier = B.identifier
where
   B.products->>'status' like '%Active%'
group by
   A.name,
   B.products

說明數據

JSON 1： {"times": ["Stoped: 49.05", "Active: 23.26"]}

JSON 2： {"times": ["Stoped: 59.05", "Active: 33.26"]}

期望的輸出： 56.52

輸出：

ERROR: could not identify an equality operator for type json

錯誤消息的直接原因是數據類型json沒有相等運算符。看：

• 如何查詢空對象的 json 列？
• 如何從 JSON 中刪除已知元素$$ $$PostgreSQL中的數組？

你有：

...
group by
   A.name,
   B.products  -- type json!?

您可以在定義相等運算符的地方使用**jsonb**而不是來做到這一點。json但是你真的要分組B.products嗎？（相同的 JSON 文件？）也許您打算寫B.products->>'status'（相同的狀態？）或者只是GROUP BY A.name？

另外：可能還有一種更簡單的方法來提取帶有REGEXP_MATCH(). 您必須定義可能的值B.products->>'status'並披露表達式的確切意圖。

如果您可以隨意這樣做，通常最好將數字儲存在單獨的鍵甚至單獨的表列中……

jsonpath在 Postgres 12 或更高版本中查詢

您添加的範例值表明您可以jsonpath在 Postgres 12 或更高版本中使用。基於jsonb（不是json）。

**注意：**這是一個概念證明。如果可能，規範化表格設計並將數字儲存在專用表格列中。更簡單，更高效。

指數

jsonpath也可以使用（預設）jsonb_opsGIN 索引來支持運算符。我用表達式縮小範圍products->'times'：

CREATE INDEX products_times_gin_idx ON products USING gin ((products->'times'));

索引僅有助於選擇性查詢，無論如何不必處理大多數行！

用於過濾符合條件的行的基本查詢jsonpath

可以使用上面的索引。

SELECT *
FROM   products B
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")';

jsonpath表達式解釋：

$[*]…查看外部嵌套級別的每個數組元素

?…執行以下測試

(@ starts with "Active: ")…元素值是否以’Active：‘開頭？

…取消嵌套並僅返回符合條件的 JSON 數組元素

SELECT *
FROM   accounts A
JOIN   products B USING (identifier)
    , jsonb_path_query(B.products->'times', '$[*] ? (@ starts with "Active: ")') act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
;

…以文本形式獲取結果

SELECT *
FROM   accounts A
JOIN   products B USING (identifier)
    , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
;

看：

• 如何將 JSON 數組轉換為 Postgres 數組？

…並聚合數字部分

到達您的最終查詢：

SELECT A.name as product, sum(right(act::text, -8)::float)  -- -8 = length('Active: ')
FROM   accounts A
JOIN   products B USING (identifier)
    , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
GROUP  BY 1;

db<>在這裡擺弄

有關的：

• 更新嵌套在 JSON 對像數組中的給定鍵的所有值

引用自：https://dba.stackexchange.com/questions/274273