我有一個 postgres 表“游戲”，其中包含游戲的不同分數。我想查詢所有游戲并獲得該特定游戲的所有分數的平均分數。我嘗試了很多不同的查詢，但由于 JSONB 資料型別，我總是遇到麻煩。游戲資料以 JSONB 格式保存，游戲表如下所示：
gameID gameInfo
---------------------------------------------------------------
1 {
"scores": [
{
"scoreType": "skill",
"score": 1
},
{
"scoreType": "speed",
"score": 3
},
{
"scoreType": "strength",
"score": 2
}
]}
2 {
"scores": [
{
"scoreType": "skill",
"score": 4
},
{
"scoreType": "speed",
"score": 4
},
{
"scoreType": "strength",
"score": 4
}
]}
3 {
"scores": [
{
"scoreType": "skill",
"score": 1
},
{
"scoreType": "speed",
"score": 3
},
{
"scoreType": "strength",
"score": 5
}
]}
預期輸出：
| 游戲編號 | 平均分 |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 2 | 3 |
我可以使用什么查詢來獲得預期的輸出？
uj5u.com熱心網友回復：
提取表示陣列的 JSONB，使用 JSONB 函式獲取 JSONB 陣列，提取字串值。
select gameid, avg(score::int) s
from (
select gameid, jsonb_array_elements(gameInfo #>'{scores}') ->'score' score
from foo
) t
group by gameid
order by gameid
uj5u.com熱心網友回復：
您也可以通過以下方式使用橫向連接：
select gameID, avg((s->>'score')::int) avg_score
from g, lateral jsonb_array_elements((gameInfo->>'scores')::jsonb) s
group by gameID
;
SQL 編輯器在線
結果：
======== ====================
| gameid | avg_score |
======== ====================
| 3 | 3.0000000000000000 |
-------- --------------------
| 2 | 4.0000000000000000 |
-------- --------------------
| 1 | 2.0000000000000000 |
-------- --------------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/504838.html
標籤：sql 数组 json PostgreSQL jsonb
上一篇：從多個表中選擇相同的多個列
下一篇：如何使用值作為列格式化sql表