Does PostgreSQL support wildcard indexes on JSON? I have a multi-tenant service with a table like this:
project_id | user_id | user_properties

Each project belongs to a different customer and customers can freely attach metadata to their users. A project may have millions of users.
Then a customer may want to find some users inside his project filtering with the user_properties (e.g. age grater than X, favorite music equal to Y, etc.)
user_properties can be an arbitrary json of key-value pairs and a customer can run arbitrary queries on the user_properties. The json is not nested (only key-value pairs).
Since a query may return many results it would also be useful to use some sort of pagination (e.g. order by user_id + limit). But pagination, together with arbitrary filters, seems an additional issue for performance...
Is it possible to handle that case in PostgreSQL? Is EAV the only solution?
I see that MongoDB supports wildcard indexes: does PostgreSQL offer anything similar?
This won't work. In particular, you cannot create a single index that will support conditions like
WHERE (user_properties -&gt;&gt; 'some random property')::date &gt;= '2000-01-01'::date

for arbitrary attributes.
You will have to identify the properties you want to use in comparisons and create specialized indexes on these properties. In the above sample, that would be:
CREATE INDEX ON mytable (((user_properties -&gt;&gt; 'some random property')::date));

It would be better to extract such columns from the JSON and have them be regular table columns.