SQL查询，用于筛选JSONB数组中所有数组项满足条件的位置

Question

我之前也发过类似的帖子，但因为有上下文错误而删除了它。

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

inserted_at{cart:[｛product_id:4，price_in_cents:1500，名称："productD"｝，{product_id:2，price-in_cents:2500，名称；"productB"}]｝

id	用户id	快照
1	37	{cart:[｛product_id:1，price_in_cents:3000，名称："产品A"｝，{product_id:2，price-in_cents:2500，名称："产品B"｝]｝
2	24	{cart:[{product_id:1，price_in_cents:3000，名称："产品A"}，{product_id:3，price-in_cents:5500，名称："产品C"}]}	2022-01-02 20:00:00000000
3	88

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

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

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

这里有一个基于原始查询思想的可能解决方案。cart 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表中，按sessions行提取并连接cart数组
• cartJSON数组的必要项然后由使用JSONB_ARRAY_ELEMENTS(jsonb)函数的第二个联接取消测试

以下操作对我来说效果很好，使我能够灵活地使用不同的比较运算符，而不仅仅是==或<=。

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

如果其他人遇到和我一样的问题，或者其他人找到了更好的解决方案，或者想在这个问题的基础上提出建议，就把这个问题留在这里。

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%";
)