在 jsonb 列的數組中查找特定值的所有出現

所以，我的目標是獲取一個值出現在列中的所有時間的計數。

我有一個帶有 jsob 類型列結果的表 ( my_table ) 。列中的值如下所示：

[
{
   "id": "b28a6f8c-b504-4623-a033-ea74d258d76f",
   "code": "S0255"

},
{
   "id": "773c571b-db7a-4219-a003-36a89dcc5cb9",
   "code": "T02816"
},
{
   "id": "ddfdb739-46f3-4640-a119-34a283ce81df",
   "code": "S0255"

}
]

結果應該是：count = 2

這是一個對像數組，因此會造成混淆（是否有可以用來測試的替代查詢？

我目前的查詢是這樣的：

SELECT COUNT(DISTINCT id) as codeCount
        FROM my_table
        CROSS JOIN jsonb_array_elements(result) as res
        WHERE res ->> 'code' = 'S0255'
        AND result != JSONB '[]'

我收到以下錯誤：

org.jkiss.dbeaver.model.sql.DBSQLException：SQL 錯誤

$$ 22023 $$: 錯誤: 無法從對像中提取元素

我該如何解決這個問題？還有其他方法可以實現這一目標嗎？

編輯：經過更多研究，我發現該列同時包含數組和普通對象值，我只添加了一個過濾器，它現在執行良好。

  SELECT COUNT(DISTINCT id) as codeCount
        FROM my_table
       CROSS JOIN jsonb_array_elements(result) as res
        WHERE res ->> 'code' = 'S0255'
        AND result != JSONB '[]'
        and  jsonb_typeof(result) = 'array'

獲得此錯誤的唯一方法是，如果其中的某些行不是my_tableJSON數組。找到有問題的行

SELECT id
FROM my_table
WHERE jsonb_typeof(result) <> 'array';

如果我正確理解您的問題，您想知道該數組有多少具有特定code值的元素。

在這種情況下，JSON 路徑查詢可能更容易處理，因為它對結構更寬鬆：

select jsonb_path_query_array(result, '$[*] ? (@.code == "S0255").id')
from my_table;

使用您的範例數據，這將返回["b28a6f8c-b504-4623-a033-ea74d258d76f", "ddfdb739-46f3-4640-a119-34a283ce81df"]. 如果result列包含不是數組的內容（或沒有code鍵的數組元素），則結果為空數組。

要從該數組中獲取元素的數量，您可以使用：

jsonb_array_length(jsonb_path_query_array(result, '$[*] ? (@.code == "S0255").id'))

您的查詢的主要區別在於，這不會DISTINCT對返回的 ID 應用 a。如果您確實需要不同的，您可以從 JSON 路徑中取消嵌套結果數組：

select count(distinct id)
from my_table
 cross join jsonb_array_elements_text(jsonb_path_query_array(result, '$[*] ? (@.code == "S0255").id')) as res(id)
where result @> '[{"code": "S0255"}]'

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