7

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
)
  • You need to provide the verbatim error message and the (relevant part of) your table definition (\d tbl in psql), as well as the query you are trying to run. For starters, this related answer on SO may be of help. That one is for indexing a json array, though. Your case seems simpler ... – Erwin Brandstetter Dec 5 '13 at 22:47
  • 1
    Thanks for your comment. I'm aware of that other answer and I'm already using this index for arrays as described there successfully. I also use another index on a json property which holds simple text. Just for this int column it doesn't work. However, using it in a query to sort as int works great. And btw, thanks for your great support here! – Christoph Dec 5 '13 at 23:41
13

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.

  • Great! That worked. There is just a closing parenthesis missing in your query. Thanks for saving my day again! – Christoph Dec 6 '13 at 9:26
  • 5
    With enough brackets it should work. Eg CREATE INDEX user_reputation_idx2 ON users ( (("user"->>'reputation')::INT) ). I recall reading an answer in the pg lists where Tom Lane explained why the expression needed the extra brackets, but a quick search didn't turn it up. – bma Dec 6 '13 at 21:59
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.