Re: [HACKERS] [PATCH] Generic type subscripting

From: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
To: Oleksandr Shulgin <oleksandr(dot)shulgin(at)zalando(dot)de>
Cc: Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Oleg Bartunov <obartunov(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, David Steele <david(at)pgmasters(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] [PATCH] Generic type subscripting
Date: 2018-03-22 22:25:02
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

> On 20 March 2018 at 11:09, Oleksandr Shulgin <oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
>> On Tue, Mar 6, 2018 at 6:21 PM, Dmitry Dolgov <9erthalion6(at)gmail(dot)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.

Attachment Content-Type Size
0001-Renaming-for-new-subscripting-mechanism-v10.patch application/octet-stream 44.8 KB
0002-Base-implementation-of-subscripting-mechanism-v10.patch application/octet-stream 127.2 KB
0003-Subscripting-for-array-v10.patch application/octet-stream 13.4 KB
0004-Subscripting-for-jsonb-v10.patch application/octet-stream 33.0 KB
0005-Subscripting-documentation-v10.patch application/octet-stream 19.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Banck 2018-03-22 23:05:51 Re: [PATCH] Verify Checksums during Basebackups
Previous Message Tom Lane 2018-03-22 21:38:53 Re: Error detail/hint style fixup