Any advice on how to best query for values within json (using 9.4). I have numeric fields within the json and want to include terms for those fields.
I've found that something like this works:
select * from atable where (json_col->>'numeric_prop')::numeric < 100;
But whilst that works: 1. seems to have unnecessary casts? The numeric _prop item is of numeric type, but its getting retrieved as text and then cast to numeric and then compared
The right operand type of the ->> oeprator is text when ->> is used to get a json object field. So the cast to numeric is needed.
2. its not going to use any index on the json_col jsonb column.
The usage of an index is mostly ruled by the 'selectivity' of the query. Anyway, if querying for particular items within the key is common (as 'numeric_prop' in your example), defining an index like this may be worthwhile:
CREATE INDEX idxgin_numeric_prop ON atable USING gin((json_col->'numeric_prop'));