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 |
Message-ID: | CA+q6zcWK=-US0TH6BOBuzNM6Tq5YGF5iW9RnkgdVB5RYtN=Ffw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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 |
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 |