Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M320292=pgsql-hackers-arkari@postgresql.org>)
	id 1cmCz7-0000B1-8H
	for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Mar 2017 05:21:01 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M320292=pgsql-hackers-arkari@postgresql.org>)
	id 1cmCz6-0000MC-Kd
	for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Mar 2017 05:21:00 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <peter.eisentraut@2ndquadrant.com>)
	id 1cmCz5-0000LB-Ke
	for pgsql-hackers@postgresql.org; Fri, 10 Mar 2017 05:20:59 +0000
Received: from out1-smtp.messagingengine.com ([66.111.4.25])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <peter.eisentraut@2ndquadrant.com>)
	id 1cmCz2-0004x5-R3
	for pgsql-hackers@postgresql.org; Fri, 10 Mar 2017 05:20:59 +0000
Received: from compute7.internal (compute7.nyi.internal [10.202.2.47])
	by mailout.nyi.internal (Postfix) with ESMTP id 4AE2820DC8;
	Fri, 10 Mar 2017 00:20:55 -0500 (EST)
Received: from frontend1 ([10.202.2.160])
  by compute7.internal (MEProxy); Fri, 10 Mar 2017 00:20:55 -0500
DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=
	messagingengine.com; h=content-transfer-encoding:content-type
	:date:from:in-reply-to:message-id:mime-version:references
	:subject:to:x-me-sender:x-me-sender:x-sasl-enc:x-sasl-enc; s=
	smtpout; bh=lZSCvTv3JtQdTPF4ZPYa6aHJ8X0=; b=LwLmQTdrtViHHtmdzMtH
	euRpR6g17l60iFq6aM3gSaujeDWgGMwlqgmTeIbfFPthFduVQxEbY5sSL3mbKidE
	RSNrXL9ZNDeFZX8fdaZWfPqUBgeCYza1vzgB7/qWv1KycGdxzJMZk1zZd3PgONY5
	cbd8+M+Qhdour7y7XOCwHMY=
X-ME-Sender: <xms:tzfCWCtMyEJa0fCXSfUgcSGv72zUtG0Hl4tZHsLRz-UV-n3DX89dsA>
X-Sasl-enc: Iq5WwkhE4FP6RxJ5Ff7iV2Iok8SK5Ems2y0H5BysUDjG 1489123255
Received: from april.local (c-73-13-66-39.hsd1.pa.comcast.net [73.13.66.39])
	by mail.messagingengine.com (Postfix) with ESMTPA id 02F187E16E;
	Fri, 10 Mar 2017 00:20:54 -0500 (EST)
Subject: Re: [PATCH] Generic type subscripting
To: Dmitry Dolgov <9erthalion6@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CA+q6zcVovR+XY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA@mail.gmail.com>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <dc125ec4-d3d4-aed8-6014-c21d5714530e@2ndquadrant.com>
Date: Fri, 10 Mar 2017 00:20:54 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:45.0)
 Gecko/20100101 Thunderbird/45.7.1
MIME-Version: 1.0
In-Reply-To: 
 <CA+q6zcVovR+XY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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