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?
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 type
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.
As @bma explained in a comment, your index will work as-is, if you add double parentheses around the entire JSON definition:
CREATE INDEX user_reputation_idx ON users((("user"->>'reputation')::int));
Make sure you consistently use the JSON text notation [->>], not the JSON object notation [->], in the index and when referencing the field in a query, or the index won’t be used.
Alternatively, you can consistently use the object notation [->]. You just have to stick to one or the other, don’t mix them.