JSONB內行中數組鍵值的索引範圍比較

這是用於有結果的測試。順序和測試是否完成可能因行而異。

column jdata
{"name": "Somedata",
"array": [ {"name":"test0","value":4.6}, 
           {"name":"test2", "value": 6.7},
           {"name":"test3", "value": 7.5},
           {"name":"test1","value":4.6}
          ],
"otherstuff": "stuff"
}

我想獲取值是 gt 4.5 的所有 test1。test1 可以在數組中的任何位置。

我能想到的最好的方法是進行索引掃描@> {"name": "test1"}以獲取 ID，然後以某種方式建構路徑(jdata->'array'->(gettest1indexsubqueryoffsorts)->'value')::float > 4.5

可以建構一個索引，將所有測試匯總到其中{"test1": 4.6, "test2": xx.xx}，但不能再次對其進行範圍檢查，或者可以更改資料結構。指針會很棒。

您想測試給定鍵的一系列數值（不僅僅是相等），但現有jsonb運算符不提供此類功能，更不用說索引支持了。

使任務難以加速，但仍有選擇。最佳解決方案取決於數據分佈、值頻率、典型查詢、平均值。列大小和其他未公開的細節。

假設您想要測試任意名稱和值並返回整行的一般情況。

SELECT *
FROM   tbl t
WHERE  EXISTS (
  SELECT FROM jsonb_array_elements(t.jdata->'array') j
  WHERE  j->>'name' = 'test1'                           -- !
  AND   (j->>'value')::numeric > 4.5
  )
AND t.jdata->'array' @> '[{"name": "test1"}]';

外部謂詞AND t.jdata->'array' @> '[{"name": "test1"}]'在邏輯上是冗餘的，只有在您期望一*小部分行甚至具有“test1”*時才有意義，在這種情況下，查詢可以很好地利用這個索引：jsonb_path_ops

CREATE INDEX tbl_jdata_arr_test1_idx ON tbl USING gin ((jdata->'array') jsonb_path_ops);

不要jsonb為此目的索引整個列，只會使索引更大更慢。僅將嵌套數組放入索引並確保WHERE查詢條件匹配。

如果您只對“test1”感興趣，請將其設為部分索引：

CREATE INDEX ...
WHERE  jdata->'array' @> '[{"name": "test1"}]';

我們必須j->>'name' = 'test1'在表達式中重複測試EXISTS以斷言"name":"test1"提供值 > 4.5 的對象 - 而不僅僅是數組中的任何對象。

另外，我寧願投到numeric，而不是float。浮點數的隱式舍入可能會使您的測試在極端情況下失敗。

有關的：

• 加快嵌套在 jsonb 對像數組中的鍵值的範圍測試

更專業、更小、更快的索引是可能的，例如通過提取一個 Postgres 名稱數組並僅對. 有關的：

• 使用更大的運算符在 jsonb 數組中搜尋嵌套值
• 在 JSONB 記錄數組中查找包含鍵的行（我對您之前相關問題的回答）
• 在 JSON 數組中查找元素的索引
• 在 JSON 數組中查找屬性組合的索引

我想你想要的是這樣的。（如果你想要完整的jdata）

SELECT tbl.*
FROM tbl
WHERE EXISTS (
 SELECT 1
 FROM jsonb_to_recordset(jdata->array) AS t(n text, v float)
 WHERE v > 9.5
) AS t(is_95)
AND jdata @> '{"name": "test1"}';

或者如果您只想要傳遞元素，則類似這樣。

SELECT *
FROM tbl
CROSS JOIN LATERAL (
 SELECT n,t
 FROM jsonb_to_recordset(jdata->array) AS t(n text, v float)
 WHERE v > 9.5
) AS t(n,t)
AND jdata @> '{"name": "test1"}';

但是當你不給我們實際數據或期望的輸出時，真的很難說。

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