Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbV0j-00078I-Bb for pgsql-committers@arkaria.postgresql.org; Mon, 04 Apr 2022 22:17:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nbV0i-0006Cp-7b for pgsql-committers@arkaria.postgresql.org; Mon, 04 Apr 2022 22:17:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbV0h-00069J-QH for pgsql-committers@lists.postgresql.org; Mon, 04 Apr 2022 22:17:20 +0000 Received: from ewsoutbound.kpnmail.nl ([195.121.94.183]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbV0d-0008OT-Vu for pgsql-committers@lists.postgresql.org; Mon, 04 Apr 2022 22:17:18 +0000 X-KPN-MessageId: e41266d8-b464-11ec-8ee3-005056992ed3 Received: from smtp.kpnmail.nl (unknown [10.31.155.5]) by ewsoutbound.so.kpn.org (Halon) with ESMTPS id e41266d8-b464-11ec-8ee3-005056992ed3; Tue, 05 Apr 2022 00:16:35 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=xs4all.nl; s=xs4all01; h=from:to:subject:mime-version:date:message-id:content-type; bh=+7QsOHcvzVy1TgCnoyYDM6dpyYG6WvosWW7zKYMRLTk=; b=N/9E+W2XO/ZxO7zfnUtpIX95GZrJzbQtvSIzwqdK9z3DVanyPvkBqx2K9YLXWFAuhmnXkc6CzVv0Q 59gGct8+CsvidOAffJ+USHnCJmA1vVr5O3+npd8bI9AwkzVFt0B6pW/nmMYYfLus75VTQS+Q0p+BQA tdVIrXlmgqOZSnQaW8SZgibjT3Ea6eziX1U2v/8wVTtvfi95fYpQgz/WX3YOkG4IMTFHx3r/Ve74hz gh+wyhWXlbc1RNWkNGfdju/gC8rpctdNfpZxoZDASXRGGB5tckPY38dvsPLWBPKx0SUl/t9TtAsmEA /vkWpou9k76gGy0V13RKg1eLK199IFA== X-KPN-VerifiedSender: Yes X-CMASSUN: 33|ovsm5y8vl0TmKmcMwu7RsYMUbEO6Pe0YBWyojyoBcYC1JmDzbXQaNTFonv6ZA+u 8tGd3bO3pthV7D94Q34tK3g== X-Originating-IP: 45.83.234.25 Received: from [192.168.178.20] (unknown [45.83.234.25]) by smtp.xs4all.nl (Halon) with ESMTPSA id f11070bc-b464-11ec-bbb9-00505699b758; Tue, 05 Apr 2022 00:17:13 +0200 (CEST) Content-Type: multipart/mixed; boundary="------------J1YZCHH8MBXHO5U9zIK0dn6B" Message-ID: Date: Tue, 5 Apr 2022 00:16:52 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.7.0 Subject: Re: pgsql: JSON_TABLE Content-Language: en-US To: Andrew Dunstan , pgsql-committers@lists.postgresql.org References: From: Erik Rijkers In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------J1YZCHH8MBXHO5U9zIK0dn6B Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Op 04-04-2022 om 22:23 schreef Andrew Dunstan: > JSON_TABLE Great that this is now committed! I notice one changed item: the NESTED-PATH-phrase does not accept an alias anymore. The JSON_PATH v59 patches still had: | NESTED PATH json_path_specification [ AS path_name ] COLUMNS ( json_table_column [, ...] ) My complaint is only half-hearted because I don't really understand what the use of such nested-path aliases are. But it's a change from the earlier patch, and the nested-path aliases are used too in the 2017-03 'Technical report ISO/IEC TR 19075-6', which is as near I have to a SQL Standard description. FWIW, I attach example sql+data from that .pdf from ISO (which is not online anymore). Thanks, Erik Rijkers --------------J1YZCHH8MBXHO5U9zIK0dn6B Content-Type: application/x-shellscript; name="bugmaybe.sh" Content-Disposition: attachment; filename="bugmaybe.sh" Content-Transfer-Encoding: base64 IyEvYmluL2Jhc2gKCnNjaGVtYT1fX2pzb25fdGFibGUKIHRhYmxlPWJvb2tjbHViIAogICAg IHQ9JHNjaGVtYS4kdGFibGUKCnNxbF9jcmVhdGVfaW5zZXJ0PSQoY2F0IDw8IFNRTF9UWFQK LS0gdGFibGUgMTggZnJvbSBJU08gLnBkZjoKZHJvcCAgIHRhYmxlIGlmIGV4aXN0cyAkdCA7 CmNyZWF0ZSB0YWJsZSAgICAgICAgICAgJHQgKAogICAgaWQgICBpbnRlZ2VyIHByaW1hcnkg a2V5CiAgLCBqY29sIGpzb25iICAgICAgICAgICAgICAgLS0gYWthICdKQ09MJyBpbiB0aGUg LnBkZgopOwoKaW5zZXJ0IGludG8gJHQgKGlkLCBqY29sKSAgdmFsdWVzIAooMTExLCAnCnsK ICAiTmFtZSIgOiAiSm9obiBTbWl0aCIsCiAgImFkZHJlc3MiIDogewogICAgInN0cmVldEFk ZHJlc3MiOiAiMjEgMm5kIFN0cmVldCIsCiAgICAiY2l0eSI6ICJOZXcgWW9yayIsCiAgICAi c3RhdGUiIDogIk5ZIiwKICAgICJwb3N0YWxDb2RlIiA6IDEwMDIxCiAgfSwKICAicGhvbmVO dW1iZXIiIDogWwogICAgeyAidHlwZSIgOiAiaG9tZSIsICJudW1iZXIiIDogIjIxMiA1NTUt MTIzNCIgfSwKICAgIHsgInR5cGUiIDogImZheCIsICJudW1iZXIiIDogIjY0NiA1NTUtNDU2 NyIgfQogIF0sCiAgImJvb2tzIiA6IFsgeyAidGl0bGUiIDogIlRoZSBUYWxpc21hbiIsCiAg ICAgICJhdXRob3JMaXN0IjogWyAiU3RlcGhlbiBLaW5nIiwgIlBldGVyIFN0cmF1YiIgXSwK ICAgICAgImNhdGVnb3J5IjogWyAiU2NpRmkiLCAiTm92ZWwiIF0KICAgIH0sCiAgICB7CiAg ICAgICJ0aXRsZSIgOiAiRmFyIGZyb20gdGhlIE1hZGRpbmcgQ3Jvd2QiLAogICAgICAiYXV0 aG9yTGlzdCIgOiBbICJUaG9tYXMgSGFyZHkiIF0sCiAgICAgICJjYXRlZ29yeSIgOiBbICJO b3ZlbCIgXQogICAgfQogIF0KfQonKSwKCigyMjIsCid7CiAgIk5hbWUiIDogIlBldGVyIFdh bGtlciIsCiAgImFkZHJlc3MiIDogeyAic3RyZWV0QWRkcmVzcyI6ICIxMTEgTWFpbiBTdHJl ZXQiLAogICAgICAgICAgICAgICAgImNpdHkiOiAiU2FuIEpvc2UiLAogICAgICAgICAgICAg ICAgInN0YXRlIiA6ICJDQSIsCiAgICAgICAgICAgICAgICAicG9zdGFsQ29kZSIgOiA5NTEx MSB9LAogICJwaG9uZU51bWJlciIgOiBbIHsgInR5cGUiIDogImhvbWUiICAsICJudW1iZXIi IDogIjQwOCA1NTUtOTg3NiIgfSwKICAgICAgICAgICAgICAgICAgICB7ICJ0eXBlIiA6ICJv ZmZpY2UiLCAibnVtYmVyIiA6ICI2NTAgNTU1LTI0NjgiIH0gXSwKICAiYm9va3MiIDogWyB7 ICJ0aXRsZSI6Ikdvb2QgT21lbnMiLAogICAgICAgICAgICAgICAgImF1dGhvckxpc3QiIDog WyAiTmVpbCBHYWltYW4iLCAiVGVycnkgUHJhdGNoZXR0IiBdLAogICAgICAgICAgICAgICAg ImNhdGVnb3J5IiA6IFsgIkZhbnRhc3kiLCAiTm92ZWwiIF0gfSwKICAgICAgICAgICAgICB7 ICJ0aXRsZSIgOiAiU21va2UgYW5kIE1pcnJvcnMiLAogICAgICAgICAgICAgICAgImF1dGhv ckxpc3QiIDogWyAiTmVpbCBHYWltYW4iIF0sCiAgICAgICAgICAgICAgICAiY2F0ZWdvcnki IDogWyJOb3ZlbCJdIH0gXQp9Cic6Ompzb25iKSwKCigzMzMsCicKewogICJOYW1lIiA6ICJK YW1lcyBMZWUiCn0KJykKU1FMX1RYVAopCgppZiBbWyAxIC1lcSAxIF1dCnRoZW4KICBlY2hv ICIkc3FsX2NyZWF0ZV9pbnNlcnQiIHwgcHNxbCAtcVgKIyBlY2hvICJzZWxlY3QgaWQsIGpz b25iX3ByZXR0eShqY29sKSBmcm9tICR0IiB8IHBzcWwgLXFYIAojIGV4aXQKZmkKCnNxbD0k KGNhdCA8PCBTUUwKLS0KLS0gIHN5bnRheCBlcnJvcnMgYXQgTkVTVEVEIFBBVEggYWxpYXNl cyBib29rcywgYXV0aCwgYW5kIGNhdAotLSAgb3V0Y29tbWVudCB0byBydW4gd2l0aG91dCBl cnJvcnMKLS0Kc2VsZWN0ICd0YWJsZSAxOCcsIGJvb2tjbHViLmlkLCBqdC5uYW1lLCBqdC50 aXRsZSwganQuYXV0aG9yLCBqdC5jYXRlZ29yeQpmcm9tIF9fanNvbl90YWJsZS5ib29rY2x1 YiBhcyBib29rY2x1YiwKICAgICBqc29uX3RhYmxlIChib29rY2x1Yi5qY29sLCAnbGF4ICQn CiAgICAgICAgICAgICAgICAgY29sdW1ucyAobmFtZSB2YXJjaGFyKDMwKSBwYXRoICdsYXgg JC4iTmFtZSInLAogICAgICAgICAgICAgICAgICAgICAgICAgIG5lc3RlZCBwYXRoICdsYXgg JC5ib29rc1sqXScgYXMgYm9va3MgICAgICAgICAgICAgICAgICAgIC0tIGVycm9yCiAgICAg ICAgICAgICAgICAgICAgICAgICAgY29sdW1ucyAodGl0bGUgdmFyY2hhcig2MCkgcGF0aCAn bGF4ICQudGl0bGUnLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIG5lc3Rl ZCBwYXRoICdsYXggJC4iYXV0aG9yTGlzdCJbKl0nICAgICAgYXMgYXV0aCAtLSBlcnJvcgog ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIGNvbHVtbnMgKGF1dGhvciB2YXJj aGFyKDMwKSBwYXRoICdsYXggJCAnKSwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICBuZXN0ZWQgcGF0aCAnbGF4ICQuY2F0ZWdvcnlbKl0nICAgICAgICAgIGFzIGNhdCAg LS0gZXJyb3IKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBjb2x1bW5zIChj YXRlZ29yeSB2YXJjaGFyKDMwKSBwYXRoICdsYXggJCAnKQogICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgKQogICAgICAgICAgICAgICAgICAgICAgICAgKQopIGFzIGp0IDsK U1FMCikKCiMgc2VydmljZSBIRUFEIGlzIDkwOWVlYmYyN2I5CmVjaG8gIiRzcWwiIHwgcHNx bCAtcVggc2VydmljZT1IRUFEICAgICAgICMgfCBtZDVzdW0KCiMgb3V0cHV0OgoKIyAgP2Nv bHVtbj8gfCBpZCAgfCAgICAgbmFtZSAgICAgfCAgICAgICAgICAgdGl0bGUgICAgICAgICAg ICB8ICAgICBhdXRob3IgICAgICB8IGNhdGVnb3J5CiMgLS0tLS0tLS0tLSstLS0tLSstLS0t LS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0t LS0tKy0tLS0tLS0tLS0KIyAgdGFibGUgMTggfCAxMTEgfCBKb2huIFNtaXRoICAgfCBUaGUg VGFsaXNtYW4gICAgICAgICAgICAgICB8IFN0ZXBoZW4gS2luZyAgICB8CiMgIHRhYmxlIDE4 IHwgMTExIHwgSm9obiBTbWl0aCAgIHwgVGhlIFRhbGlzbWFuICAgICAgICAgICAgICAgfCBQ ZXRlciBTdHJhdWIgICAgfAojICB0YWJsZSAxOCB8IDExMSB8IEpvaG4gU21pdGggICB8IFRo ZSBUYWxpc21hbiAgICAgICAgICAgICAgIHwgICAgICAgICAgICAgICAgIHwgU2NpRmkKIyAg dGFibGUgMTggfCAxMTEgfCBKb2huIFNtaXRoICAgfCBUaGUgVGFsaXNtYW4gICAgICAgICAg ICAgICB8ICAgICAgICAgICAgICAgICB8IE5vdmVsCiMgIHRhYmxlIDE4IHwgMTExIHwgSm9o biBTbWl0aCAgIHwgRmFyIGZyb20gdGhlIE1hZGRpbmcgQ3Jvd2QgfCBUaG9tYXMgSGFyZHkg ICAgfAojICB0YWJsZSAxOCB8IDExMSB8IEpvaG4gU21pdGggICB8IEZhciBmcm9tIHRoZSBN YWRkaW5nIENyb3dkIHwgICAgICAgICAgICAgICAgIHwgTm92ZWwKIyAgdGFibGUgMTggfCAy MjIgfCBQZXRlciBXYWxrZXIgfCBHb29kIE9tZW5zICAgICAgICAgICAgICAgICB8IE5laWwg R2FpbWFuICAgICB8CiMgIHRhYmxlIDE4IHwgMjIyIHwgUGV0ZXIgV2Fsa2VyIHwgR29vZCBP bWVucyAgICAgICAgICAgICAgICAgfCBUZXJyeSBQcmF0Y2hldHQgfAojICB0YWJsZSAxOCB8 IDIyMiB8IFBldGVyIFdhbGtlciB8IEdvb2QgT21lbnMgICAgICAgICAgICAgICAgIHwgICAg ICAgICAgICAgICAgIHwgRmFudGFzeQojICB0YWJsZSAxOCB8IDIyMiB8IFBldGVyIFdhbGtl ciB8IEdvb2QgT21lbnMgICAgICAgICAgICAgICAgIHwgICAgICAgICAgICAgICAgIHwgTm92 ZWwKIyAgdGFibGUgMTggfCAyMjIgfCBQZXRlciBXYWxrZXIgfCBTbW9rZSBhbmQgTWlycm9y cyAgICAgICAgICB8IE5laWwgR2FpbWFuICAgICB8CiMgIHRhYmxlIDE4IHwgMjIyIHwgUGV0 ZXIgV2Fsa2VyIHwgU21va2UgYW5kIE1pcnJvcnMgICAgICAgICAgfCAgICAgICAgICAgICAg ICAgfCBOb3ZlbAojICB0YWJsZSAxOCB8IDMzMyB8IEphbWVzIExlZSAgICB8ICAgICAgICAg ICAgICAgICAgICAgICAgICAgIHwgICAgICAgICAgICAgICAgIHwKIyAoMTMgcm93cykKCg== --------------J1YZCHH8MBXHO5U9zIK0dn6B--