JPA @ElementCollection how can I query?

You can't directly query from @ElementCollection. You should query base entity (I assume its name is Subscription).

@Query("select s from Subscription s where s.categories = ?1")
List<Subscription> findUsernameByCategory(String category);

If you want query by key, then use

@Query("select s from Subscription s where index(s.categories) = ?1")
List<Subscription> findUsernameByCategoryKey(Integer key);

I'd also side with @talex on this and argue that you need to base your query on the parent/container/base object of the @ElementCollection.

In my experience following query should suffice:

@Query("select category from Subscription subscription inner join subscription.categories category")

Side-note: Querying subscription_categories seems to be the wrong path, since this table is part of a different layer (the database layer in Sql/Jpql), while the query should be formed on the Hibernate layer (hql), which uses your entity/class-names as references. I have used Upper-case class names, instead of lower-case table names.