用于过滤 JSONB 数组中所有数组项满足条件的 SQL 查询

Question

我以前发过类似的帖子，但由于上下文错误而删除了它。

我的数据库中的一个表包含一个 JSONB 列，其中包含一组 JSON 对象。它与我在下面模拟的这个 session 表示例没有什么不同。

id	user_id	snapshot	insert_at
1	37	{购物车：[{product_id：1，price_in_cents：3000，name：“产品A”}，{product_id：2，price_in_cents：2500，name：“产品B”}]}	2022-01-01 20:00:00.000000
2	24	{购物车： [{product_id：1，price_in_cents：3000，名称：“产品 A”}，{product_id：3，price_in_cents：5500，名称：“产品 C”}]}	2022-01-02 20:00:00.000000
3	88	{cart ：[{product_id：4，price_in_cents：1500， name: "product D"}, {product_id: 2, Price_in_cents: 2500, name: "product B"}]}	2022-01-03 20:00:00.000000

我用来从此表中检索记录的查询是如下。

SELECT sessions.*
FROM sessions
INNER JOIN LATERAL (
    SELECT *
    FROM jsonb_to_recordset(sessions.snapshot->'cart')
    AS product(
        "product_id" integer,
        "name" varchar,
        "price_in_cents" integer
    )
) AS cart ON true;

我一直在尝试更新上面的查询，以仅检索会话表中购物车中所有产品的 price_in_cents 值大于 <代码>2000。

到目前为止，我在形成这个查询方面还没有取得任何成功，但如果这里有人能给我指出正确的方向，我将不胜感激。

Answer 1

您可以使用 JSON 路径表达式：

select *
from sessions
  ...
where not sessions.snapshot @@ '$.cart[*].price_in_cents <= 2000'

没有 JSON 路径表达式可以检查所有数组元素是否大于 2000。因此，这将返回没有元素小于 2000 的那些行 - 因为可以使用 JSON 路径表达式来表达。

Answer 2

这是基于您原始查询的想法的一种可能的解决方案。
购物车 JSON 数组对象的每个元素都连接到其 sessions 父行。既然所需的 JSON 数组元素已公开，您就需要添加 WHERE 子句条件。

SELECT *
FROM (
  SELECT
    sess.id,
    sess.user_id,
    sess.inserted_at,
    cart_items.cart_name,
    cart_items.cart_product_id,
    cart_items.cart_price_in_cents
  FROM sessions sess,
    LATERAL (SELECT (snapshot -> 'cart') snapshot_cart FROM sessions WHERE id = sess.id) snap_arr,
    LATERAL (SELECT
               (value::jsonb ->> 'name')::text cart_name,
               (value::jsonb -> 'product_id')::int cart_product_id,
               (value::jsonb -> 'price_in_cents')::int cart_price_in_cents
             FROM JSONB_ARRAY_ELEMENTS(snap_arr.snapshot_cart)) cart_items
) session_snapshot_cart_product;

解释：

• 从 sessions 表中，提取 cart 数组，并按 sessions 行连接
• cart 的必要项目> 然后，使用 JSONB_ARRAY_ELEMENTS(jsonb) 函数通过第二个连接解除 JSON 数组的嵌套

Answer 3

以下内容对我来说效果很好，并允许我灵活地使用不同的比较运算符，而不仅仅是 == 或 <= 等运算符。

在我需要构建的一个场景中，我还需要使用 IN 比较运算符将子查询中的 WHERE 与值数组进行比较，这不是使用我们研究过的一些其他解决方案是可行的。

将其留在这里，以防其他人遇到与我相同的问题，或者其他人找到更好的解决方案或想要在此基础上提出建议。

SELECT *
FROM sessions
WHERE NOT EXISTS (
    SELECT sessions.*
    FROM sessions
    INNER JOIN LATERAL (
        SELECT *
        FROM jsonb_to_recordset(sessions.snapshot->'cart')
        AS product(
            "product_id" integer,
            "name" varchar,
            "price_in_cents" integer
        )
    ) AS cart ON true
    WHERE name ILIKE "Product%";
)