On 2/28/17 13:02, Dmitry Dolgov wrote:
> +<programlisting>
> +-- Extract value by key
> +SELECT ('{"a": 1}'::jsonb)['a'];
> +
> +-- Extract nested value by key path
> +SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
> +
> +-- Extract element by index
> +SELECT ('[1, "2", null]'::jsonb)['1'];
> +
> +-- Update value by key
> +UPDATE table_name set jsonb_field['key'] = 1;
> +
> +-- Select records using where clause with subscripting
> +SELECT * from table_name where jsonb_field['key'] = '"value"';
> +</programlisting>
I see a possible problem here: This design only allows one subscripting
function. But what you'd really want in this case is at least two: one
taking an integer type for selecting by array index, and one taking text
for selecting by field name.
I suppose that since a given value can only be either an array or an
object, there is no ambiguity, but I think this might also lose some
error checking. It might also not work the same way for other types.
It looks like your jsonb subscripting function just returns null if it
can't find a field, which is also a bit dubious.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services