Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1erOy4-0002RD-6e for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Mar 2018 14:13:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1erOy2-0001nr-Rd for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Mar 2018 14:13:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1erOy2-0001lk-HB for pgsql-hackers@lists.postgresql.org; Thu, 01 Mar 2018 14:13:54 +0000 Received: from mail.thelabyrinth.net ([45.56.70.56]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1erOxz-0001UZ-5M for pgsql-hackers@lists.postgresql.org; Thu, 01 Mar 2018 14:13:53 +0000 Received: from Davids-iMac.local (unknown [173.169.74.224]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: dsteele) by mail.thelabyrinth.net (Postfix) with ESMTPSA id 84F54546F7; Thu, 1 Mar 2018 14:13:49 +0000 (UTC) Subject: Re: Re: Cast jsonb to numeric, int, float, bool To: Nikita Glukhov , Darafei Praliaskouski , Anastasia Lubennikova , pgsql-hackers@lists.postgresql.org References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru> <9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru> <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org> <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru> From: David Steele Message-ID: Date: Thu, 1 Mar 2018 09:13:48 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 In-Reply-To: <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2/28/18 7:12 PM, Nikita Glukhov wrote: > On 01.03.2018 00:43, Darafei Praliaskouski wrote: >> >> The new status of this patch is: Ready for Committer > > Attached new version of the patch in which I removed duplicated code > using new subroutine JsonbExtractScalar(). I am not sure what is better > to do when a JSON item has an unexpected type: to throw an error or to > return SQL NULL. Also JSON nulls could be converted to SQL NULLs. I > should note here that expression (jb -> 'key')::datatype can be > rewritten with SQL/JSON function JSON_VALUE: JSON_VALUE(jb, '$.key' > RETURNING datatype ERROR ON ERROR) But by standard JSON_VALUE tries to > cast string JSON items to the specified datatype too, so > JSON_VALUE('{"key": "123"}'::jsonb, '$.key' RETURNING int ERROR ON > ERROR) does not throw an error but returns 123. We already have jsonpath > operators @#, @*, so it might be very useful if our jsonb casts were > equivalent to theirs SQL/JSON analogues. For example, (jb @# > '$.key')::datatype could be equivalent to JSON_VALUE(jb, '$.key' > RETURNING datatype ERROR ON ERROR) or JSON_VALUE(jb, '$.key' RETURNING > datatype [NULL ON ERROR]). But if we want to have NULL ON ERRORbehavior > (which is default in SQL/JSON) in casts, then casts should not throw any > errors. Since this patch was updated after being set a Ready for Committer and there appear to be some open questions, I have set it to Needs Review. Thanks, -- -David david@pgmasters.net