ANY/ALL (array) does not support set arguments

Question

I have a jsonb column that has a details about a promotional sale on them.

I would like to filter the keys to be that which are within a predefined list.

I applied what was intuitive to me, a where code in ('foo', 'bar') but that raises an error: ANY/ALL (array) does not support set arguments

Any ideas?

Query:

select
    upper(jsonb_object_keys(orders.extra_details -> 'promotion_codes')) as promo_code,
    count(tickets.id)
from 
    tickets
    inner join order_items on order_items.orderable_id = tickets.id
    inner join orders on orders.id = order_items.order_id
where 
    order_items.type = 'TicketOrderItem'
    and orders.extra_details -> 'promotion_codes' is not null
    and tickets.event_id = 4062
    and tickets.status = 2
    and upper(jsonb_object_keys(orders.extra_details -> 'promotion_codes')) in ('FOO', 'BAR', 'BAZ')
group by 
    1
order by 
    1

Answer 1

jsonb_object_keys returns a set of values. You shouldn't use that in the select list and you can't really use it in the where clause.

It's like using an expression like this: where (select some_column from some_table) in ('a','b')

The correct way to use it, is to join your query against the result of jsonb_object_keys

select upper(ed.promo_code) as promo_code,
       count(tickets.id)
from tickets
  join order_items on order_items.orderable_id = tickets.id
  join orders on orders.id = order_items.order_id
  join lateral jsonb_object_keys(orders.extra_details -> 'promotion_codes') as ed(promo_code) on true
where 
    order_items.type = 'TicketOrderItem'
    and orders.extra_details -> 'promotion_codes' is not null
    and tickets.event_id = 4062
    and tickets.status = 2
    and upper(ed.promo_code) in ('FOO', 'BAR', 'BAZ')
group by 
    1
order by 
    1