在 JSONB 記錄數組中查找包含鍵的行

我正在嘗試查詢對像數組中存在的鍵。這種結構：

column jdata
{"name": "Somedata",
"array": [ {"name":"bla1", "attr": "somevalue"}, 
           {"name":"bla2", "otherdata": "somevalue2"},
           {"name":"bla3", "otherdata": "somevalue"}
          ],
"otherstuff": "stuff"
}

現在我做 btree’s on jdata->'name'or(jdata->'datetime')::cast並且效果很好。

我也做 json_path_opsjdata->'array' @> '[{"name":"bla3"}]'真正有魅力的地方。

我的問題是attr鍵可以在數組中的任何對像中，如果鍵存在，我關心記錄，但是值幾乎可以是任何東西。有沒有辦法查詢這個？有沒有辦法可以索引？我想做jdata->'array' @> '[{"attr": ?}]'或者也許? 'attr'可以在數組中以某種方式使用？

目前我正在考慮一個掃描密鑰的觸發器，然後將其移動到具有真假或其他任何內容的標題，然後正常的 btree 將起作用。有沒有更好的辦法？我需要在平均站點編輯大約 500k 條記錄以添加此值。

請指點我一個方向。

Postgres 12 或更高版本：使用 SQL/JSON 路徑表達式

SELECT *
FROM   tbl
WHERE  jdata->'array' @? '$ ? (exists (@."attr"))';

您只能在外部嵌套級別或遞歸抽像數組中查找鍵或值（取消嵌套它們）在lax模式或不模式（strict模式）。我在小提琴中添加了查詢變體來展示：

db<>在這裡擺弄

**@?**是jsonpath存在運算符

jsonpath價值'$ ? (exists (@."attr"))'解釋：

$…查看在左操作數中找到的每個值（在預設的“lax”模式下）

?…執行以下測試

(exists (@."attr"))…名稱為“attr”的鍵是否存在？

可以使用索引，或者預設jsonb_ops：

CREATE INDEX tbl_jdata_array_idx ON tbl USING GIN ((jdata->'array'));

或與jsonb_path_ops：

CREATE INDEX tbl_jdata_array_path_ops_idx ON tbl USING GIN ((jdata->'array') jsonb_path_ops);

看：

• https://www.postgresql.org/docs/current/gin-builtin-opclasses.html
• https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

有關的：

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

Postgres 11 或更早版本（還沒有 SQL/JSON）

內置運算符的普通索引未涵蓋該特定案例。

不支持索引的簡單查詢

SELECT *
FROM   tbl
WHERE  EXISTS (
  SELECT FROM jsonb_array_elements(jdata->'array') elem
  WHERE  elem ? 'attr' 
  );

EXISTS因為我們希望每個符合條件的行一次，即使多個數組元素可以包含鍵。而且速度更快。

但是這個查詢不能使用索引。

表達索引

您可以在給定的 jsonb 記錄數組中生成唯一鍵的文本數組 - 並將表達式包裝成一個簡單的IMMUTABLE函式：

CREATE OR REPLACE FUNCTION jsonb_arr_record_keys(jsonb)
 RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY (
  SELECT DISTINCT k
  FROM   jsonb_array_elements($1) elem, jsonb_object_keys(elem) k
  )';

COMMENT ON FUNCTION jsonb_arr_record_keys(jsonb) IS '
  Generates text array of unique keys in jsonb array of records.
  Fails if any array element is not a record!';

然後根據這個函式創建一個 GIN 表達式索引：

CREATE INDEX tbl_special_idx ON tbl USING gin (jsonb_arr_record_keys(jdata->'array'));

像這樣查詢，使用泛型數組 contains 運算符@>：

SELECT *
FROM   tbl
WHERE  jsonb_arr_record_keys(jdata->'array') @> '{attr}';

現在可以有效地使用索引。

提供嵌套在數組 ( '{attr}') 中的鍵名。'{attr1, attr2}'（您可以通過這種方式（ ）或類似方式方便地檢查多個鍵…）

dbfiddle在這裡

有關的：

• 在 JSON 數組中查找元素的索引
• 如何從 PostgreSQL 中的 jsonb 數組中獲取特定對象？
• 有條件地替換 jsonb 列中每行的單個值

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