從JSONB列中的多個陣列中過濾掉物件

我有一個 JSON 結構，其中兩個陣列保存在 JSONB 列中。有點簡化它看起來像這樣

{
  "prop1": "abc",
  "prop2": "xyz",
  "items": [
    {
      "itemId": "123",
      "price": "10.00"
    },
    {
      "itemId": "124",
      "price": "9.00"
    },
    {
      "itemId": "125",
      "price": "8.00"
    }
  ],
  "groups": [
    {
      "groupId": "A",
      "discount": "20",
      "discountId": "1"
    },
    {
      "groupId": "B",
      "discount": "30",
      "discountId": "2"
    },
    {
      "groupId": "B",
      "discount": "20",
      "discountId": "3"
    },
    {
      "groupId": "C",
      "discount": "40",
      "discountId": "4"
    }
  ]
}

架構：

CREATE TABLE campaign
  (
     id       TEXT PRIMARY KEY,
     data     JSONB
  );

由于每一行（資料列）可能相當大，我試圖從items和groups陣列中過濾掉匹配的專案物件和組物件。

我目前的查詢是這樣的

SELECT * FROM campaign
WHERE 
(data -> 'items' @> '[{"productId": "123"}]') OR
(data -> 'groups' @> '[{"groupId": "B"}]')

它回傳包含匹配組或匹配項的行。但是，根據行的不同，該data列可能是一個相當大的 JSON 物件（可能有數百個物件items和數十個物件，groups在本示例中為了簡潔起見我省略了幾個鍵/屬性），這會影響查詢性能（我已經在items和groups陣列上添加了 GIN 索引，所以缺少索引不是它慢的原因）。

如何過濾掉items和groups陣列以僅包含匹配元素？

鑒于此匹配行

{
  "prop1": "abc",
  "prop2": "xyz",
  "items": [
    {
      "itemId": "123",
      "price": "10.00"
    },
    {
      "itemId": "124",
      "price": "9.00"
    },
    {
      "itemId": "125",
      "price": "8.00"
    }
  ],
  "groups": [
    {
      "groupId": "A",
      "discount": "20",
      "discountId": "1"
    },
    {
      "groupId": "B",
      "discount": "30",
      "discountId": "2"
    },
    {
      "groupId": "B",
      "discount": "20",
      "discountId": "3"
    },
    {
      "groupId": "C",
      "discount": "40",
      "discountId": "4"
    }
  ]
}

我希望結果是這樣的（匹配的專案/組可能與列的其余部分位于不同的data列中 - 不必在具有兩個這樣的陣列的單個 JSON 物件中回傳，但我會如果不影響性能或導致非常麻煩的查詢，則更喜歡它）：

{
  "prop1": "abc",
  "prop2": "xyz",
  "items": [
    {
      "itemId": "123",
      "price": "10.00"
    }
  ],
  "groups": [
    {
      "groupId": "B"
      "discount": "20",
      "discountId": "3"
    }
  ]
}

到目前為止我設法做的是items使用這個查詢解包和匹配陣列中的一個物件，它從data列中洗掉“items”陣列并將匹配的item物件過濾到一個單獨的列中，但我正在努力加入這與groups陣列中的匹配項。

SELECT data - 'items', o.obj
FROM campaign c
CROSS JOIN LATERAL jsonb_array_elements(c.data #> '{items}') o(obj)
WHERE o.obj ->> 'productId' = '124'

如何在一個查詢中過濾兩個陣列？

額外問題：對于groups陣列，discount如果可能，我還想回傳具有最低值的物件。否則，結果將需要是一組匹配的組物件，而不是單個匹配的group.

相關問題：如何過濾jsonb陣列元素以及如何在Postgres中加入jsonb陣列元素？

uj5u.com熱心網友回復：

如果你的 postgres 版本是 12 或更高，你可以使用 jsonpath語言和函式。下面的查詢使用符合給定條件的專案和組的子集回傳預期結果。然后，您可以在 sql 函式中調整此查詢，使搜索條件成為輸入引數。

SELECT jsonb_set(jsonb_set( data
                          , '{items}'
                          , jsonb_path_query_array(data, '$.items[*] ? (@.itemId == "123" && @.price == "10.00")'))
                , '{groups}'
                , jsonb_path_query_array(data, '$.groups[*] ? (@.groupId == "B" && @.discount == "20" && @.discountId == "3")'))
  FROM (SELECT
'{
  "prop1": "abc",
  "prop2": "xyz",
  "items": [
    {
      "itemId": "123",
      "price": "10.00"
    },
    {
      "itemId": "124",
      "price": "9.00"
    },
    {
      "itemId": "125",
      "price": "8.00"
    }
  ],
  "groups": [
    {
      "groupId": "A",
      "discount": "20",
      "discountId": "1"
    },
    {
      "groupId": "B",
      "discount": "30",
      "discountId": "2"
    },
    {
      "groupId": "B",
      "discount": "20",
      "discountId": "3"
    },
    {
      "groupId": "C",
      "discount": "40",
      "discountId": "4"
    }
  ]
}' :: jsonb) AS d(data)
WHERE jsonb_path_exists(data, '$.items[*] ? (@.itemId == "123" && @.price == "10.00")')
  AND jsonb_path_exists(data, '$.groups[*] ? (@.groupId == "B" && @.discount == "20" && @.discountId == "3")')

標籤：sql 数组 PostgreSQL的 jsonb

上一篇：將psql文本列轉換為tsvector

下一篇：獲取不同行中沒有某些值的暫存器