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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
