How to query a json field in a joint model

[jwoodrow]

Suppose two models

class Category
  has_many :products
end

class Product
  belongs_to :cart
end

where Product and Category would also have a jsonb field attributes (for example)

How would one go about doing something like this using this gem (if at all possible)

SELECT categories.*
INNER JOIN products
  ON products.category_id = categories.id
WHERE (products.attributes->>'price'): numeric > 12.5

as a quick TL;DR how would one go to:

• handle querying the json field inside a joined table
• how to handle column name collision (how to specidy the table_name for the store query ?)

I think the solution to the second one could at least manually solve the first question too

[palkan]

Wouldn't the following work?

Category.joins(:products).merge(Product.where.store(:attributes).path(:price, 1.25))

Note that > operator is not supported (at least, yet, see #15).

[jwoodrow]

You are correct, I'm finding it hard to explain why merge didn't come to mind 😅

Thanks !