Re: querying within json

Hi Tim,

2014-10-30 15:55 GMT+01:00 Tim Dudgeon <tdudgeon(dot)ml(at)gmail(dot)com>:

> 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'));

Regards,
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it