> On 20 March 2018 at 11:09, Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote:
>> On Tue, Mar 6, 2018 at 6:21 PM, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
>>
>>
>> One more small update after fd1a421fe6 in attachments.
>
>
> Before looking at the code I have a few comments about documentation:
>
> ...
>
> In other words, I would like to see this part of documentation to be
> extended beyond just showcasing the syntax.
Good point, thanks for noticing. The thing is that the implementation of
subscripting for jsonb data type in this patch relies on the `setPath` function
and follows the same rules as e.g. `jsonb_set`, but I need to mention this
explicitly in the documentation. Speaking about your questions:
> +-- Extract value by key
> +SELECT ('{"a": 1}'::jsonb)['a'];
>
> What is the result of running this query? What is the resulting data type?
>
Jsonb subscripting expression always returns another jsonb
> +-- Extract element by index
> +SELECT ('[1, "2", null]'::jsonb)['1'];
>
> What is the result here? Why subscript is a string and not a number? Are
> subscription indexes 0- or 1-based?
>
For jsonb arrays an index is 0 based. It's also not necessary to have an index
as a string in this situation (so `data['1']` and `data[1]` are actually equal)
> +-- Select records using where clause with subscripting
> +SELECT * from table_name where jsonb_field['key'] = '"value"';
>
> Use of double quotes around "value" requires some explanation, I think.
In case of comparison, since a subscripting expression returns something of
jsonb data type, we're going to compare two objects of type jsonb. Which means
we need to convert 'value' to a jsonb scalar, and for that purpose it should be
in double quotes.
> Should the user expect that a suitable index is used by the query planner
> for this query?
There is no specific indexing support for subscripting expressions, so if you
need you can create a functional index using it.
Here is the updated version of patch, rebased after recent conflicts and with
suggested documentation improvements.
