Nested value query inside rails where

Question

to a query @products = @products.offset(offset).limit(@limit).where(status: "visible").where.not(inventory: [nil, {}])

how can I add where.not((product.inventory.length == 1 and product.inventory.first[1]["quantity"].to_i == 0))))) ?

i.e. how to check length of property inventory and if 1 check first entry's quantity, and satisfy the condition above?

Answer 1

Use jsonb operators

@products = 
  @products
    .offset(offset)
    .limit(@limit)
    .where(status: "visible")
    .where.not(inventory: [nil, {}])
    .where.not("ARRAY(select (jsonb_each(inventory)).value->>'quantity' as integer)='{0}'")