How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer.

I tried it this way (and also dozens of others)

CREATE INDEX user_reputation_idx ON users(("user"->>'reputation')::int)

It works just fine in a query (e.g. ORDER BY ("user"->>'reputation')::int)

What am I missing?

UPDATE

I'm getting a simple syntax error, however, I really have no clue why.

ERROR:  syntax error at or near "::"
LINE 1: ... user_reputation_idx ON users (("user"->>'reputation')::int)

The table definition is quite simple. It's just one column user of typejson.

So, looks like this:

CREATE TABLE users
(
  "user" json
)

Try this instead:

CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int));

The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se.

Either way, you can still use the syntax shortcut expression::type in expressions that utilize the index.

StackExchange Database Administrators Q#54539, answer score: 16