Sql 用于过滤嵌套对象或数组中的JSON数据的WHERE条件

我在PostgreSQL数据库中有一个表

CREATE TABLE js.orders (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
)

包含6行JSON数据:

select * from js.orders;

退货

 id |                                                info                                                
----+----------------------------------------------------------------------------------------------------
  1 | { "customer": "Kapil", "items": {"product": "Heineken","qty": 6}}
  2 | { "customer": "Satyen", "items": {"product": "Heineken","qty": 18}}
  3 | { "customer": "Rekha", "items": {"product": "Carlsberg","qty": 24}}
  4 | { "customer": "Madhuri", "items": {"product": "Kalyani","qty": 12}}
  5 | { "customer": "Srinivas", "items": {"product": "Kingfisher Strong","qty": 12}}
  6 | { "customer": "Saina", "items": [{"product": "Bira91","qty": 6},{"product": "Kalyani","qty": 6} ]}
(6 rows)

以下带有"WHERE"条件的"SELECT"查询:

SELECT info ->> 'customer' AS customer FROM js.orders WHERE info -> 'items' ->> 'product' = 'Heineken';

退货 the correct two rows:

customer 
----------
 Kapil
 Satyen
(2 rows)

但是，以下查询应返回两行:

SELECT info ->> 'customer' AS customer FROM js.orders WHERE info -> 'items' ->> 'product' = 'Kalyani';

but 退货 only one row:

    customer     
-----------------
 Madhuri
(1 row)

显然，"Customer":"Sania"的行有一个"Items"列表，而不是一个单独的"Items"，这是导致这个问题的原因.我应该如何修改我的查询以返回正确的行数.或者是数据必须重新格式化，否则.

站台是

'PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit'