Thread: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
Hi, FYI: I'd just like to point you to following two forthcoming standard parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on "Multi-Dimensional Arrays" (SQL/MDA). They define there some things different as already in PG. See also Peter Baumann's slides [1] and e.g. [2] :Stefan [1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql [2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Pavel Stehule
Date:
Hi
2016-06-29 1:51 GMT+02:00 Stefan Keller <sfkeller@gmail.com>:
Hi,
FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).
They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]
:Stefan
[1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql
[2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf
Can be nice if we can write API with respects ANSI SQL. The main problem is query language - we doesn't support JSONPath. But if we have a own regular expression library, probably we can have i own implementation of JSONPath
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Oleg Bartunov
Date:
On Wed, Jun 29, 2016 at 2:51 AM, Stefan Keller <sfkeller@gmail.com> wrote: > Hi, > > FYI: I'd just like to point you to following two forthcoming standard > parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on > "Multi-Dimensional Arrays" (SQL/MDA). > > They define there some things different as already in PG. See also > Peter Baumann's slides [1] and e.g. [2] I' very dissapointed with this. > > :Stefan > > [1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql > [2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
On 1 July 2016 at 17:52, Oleg Bartunov <obartunov@gmail.com> wrote:
On Wed, Jun 29, 2016 at 2:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:
> Hi,
>
> FYI: I'd just like to point you to following two forthcoming standard
> parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
> "Multi-Dimensional Arrays" (SQL/MDA).
>
> They define there some things different as already in PG. See also
> Peter Baumann's slides [1] and e.g. [2]
I' very dissapointed with this.
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Andres Freund
Date:
On 2016-07-01 19:06:06 +0800, Craig Ringer wrote: > On 1 July 2016 at 17:52, Oleg Bartunov <obartunov@gmail.com> wrote: > > > On Wed, Jun 29, 2016 at 2:51 AM, Stefan Keller <sfkeller@gmail.com> wrote: > > > Hi, > > > > > > FYI: I'd just like to point you to following two forthcoming standard > > > parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on > > > "Multi-Dimensional Arrays" (SQL/MDA). > > > > > > They define there some things different as already in PG. See also > > > Peter Baumann's slides [1] and e.g. [2] > > > > I' very dissapointed with this. > > > > > Yeah, but since when has the SQL standard adopted any existing > implementation's spelling of a feature? It seems to be politically > impossible. Especially if nobody really lobbies on part of postgresql.
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Peter Eisentraut
Date:
On 7/1/16 7:06 AM, Craig Ringer wrote: > Yeah, but since when has the SQL standard adopted any existing > implementation's spelling of a feature? It seems to be politically > impossible. The SQL/JSON thing is pretty much straight from Oracle and Microsoft (and notably completely different from DB2). -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Andreas Karlsson
Date:
On 07/01/2016 07:31 PM, Andres Freund wrote: >> Yeah, but since when has the SQL standard adopted any existing >> implementation's spelling of a feature? It seems to be politically >> impossible. > > Especially if nobody really lobbies on part of postgresql. Has any of the major PostgreSQL companies looked into sending members to the ISO committee? It would be nice if we could be represented. Andreas
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Pavel Stehule
Date:
Hi
2016-07-01 20:46 GMT+02:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>:
On 7/1/16 7:06 AM, Craig Ringer wrote:Yeah, but since when has the SQL standard adopted any existing
implementation's spelling of a feature? It seems to be politically
impossible.
The SQL/JSON thing is pretty much straight from Oracle and Microsoft (and notably completely different from DB2).
I checked standard, and it looks like not a significant problem to implement it in Postgres. The implementation should be similar to XML - requires parser support. We doesn't use a identifier of important SQL/JSON functions: JSON_EXISTS, JSON_VALUE, JSON_QUERY, JSON_TABLE, JSON_ARRAY. The problem should be with function JSON_OBJECT - standard is based on variadic function of pairs (name, value), but it should be solvable, because our first argument is a array, what is not possible in standard. So ANSI SQL conform implementation of JSON support is still possible in Postgres.
Regards
Pavel
--
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
Andreas Karlsson <andreas@proxel.se> writes:
> Has any of the major PostgreSQL companies looked into sending members to
> the ISO committee? It would be nice if we could be represented.
Peter E. had observer status at one point, don't know if he still does.
regards, tom lane
On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote: > Hi, > > FYI: I'd just like to point you to following two forthcoming standard > parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on > "Multi-Dimensional Arrays" (SQL/MDA). > > They define there some things different as already in PG. See also > Peter Baumann's slides [1] and e.g. [2] > > :Stefan > > [1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql > [2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf Thanks for these pointers. On the "standards under development" page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60 "Close of voting/ comment period". But ISO/IEC CD 9075-15 (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to Working Group". Is that how they say "returned with feedback"? ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage 30.60. Does anyone know what that one is about? Maybe something like MATCH_RECOGNIZE in Oracle? [1] http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&development=on -- Thomas Munro http://www.enterprisedb.com
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Peter Eisentraut
Date:
On 7/4/16 12:44 AM, Thomas Munro wrote: > ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage > 30.60. Does anyone know what that one is about? Maybe something like > MATCH_RECOGNIZE in Oracle? That's exactly what that is. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Peter Eisentraut
Date:
On 7/1/16 10:42 PM, Andreas Karlsson wrote: > On 07/01/2016 07:31 PM, Andres Freund wrote: >>> Yeah, but since when has the SQL standard adopted any existing >>> implementation's spelling of a feature? It seems to be politically >>> impossible. >> >> Especially if nobody really lobbies on part of postgresql. > > Has any of the major PostgreSQL companies looked into sending members to > the ISO committee? It would be nice if we could be represented. It has been considered from time to time, but it would require some significant time investment from people whose time is better spent elsewhere. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Peter Eisentraut
Date:
On 7/2/16 12:40 AM, Pavel Stehule wrote: > So ANSI SQL conform implementation of JSON support is still possible in > Postgres. It is possible, but in this case I think it's not worth it. Our built-in stuff is significantly simpler and more powerful and already in widespread use. It is worth keeping an eye on it and not create gratuitous conflicts. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Pavel Stehule
Date:
2016-07-06 5:17 GMT+02:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>:
On 7/2/16 12:40 AM, Pavel Stehule wrote:So ANSI SQL conform implementation of JSON support is still possible in
Postgres.
It is possible, but in this case I think it's not worth it. Our built-in stuff is significantly simpler and more powerful and already in widespread use.
I can't to agree. JSONPath is little bit powerful than our JSON path expression. JSON_TABLE is pretty good function for extracting values from JSON (note: I missing XML_TABLE too much).
More - Postgres is not famous due not respecting standard.
Regards
Pavel
It is worth keeping an eye on it and not create gratuitous conflicts.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Stefan Keller
Date:
Thomas 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: > ... But ISO/IEC CD 9075-15 > (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to > Working Group". Is that how they say "returned with feedback"? > ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage > 30.60. Does anyone know what that one is about? Maybe something like Peter surely would know: https://www.jacobs-university.de/directory/pbaumann :Stefan 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: > On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote: >> Hi, >> >> FYI: I'd just like to point you to following two forthcoming standard >> parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on >> "Multi-Dimensional Arrays" (SQL/MDA). >> >> They define there some things different as already in PG. See also >> Peter Baumann's slides [1] and e.g. [2] >> >> :Stefan >> >> [1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql >> [2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf > > Thanks for these pointers. On the "standards under development" > page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60 > "Close of voting/ comment period". But ISO/IEC CD 9075-15 > (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to > Working Group". Is that how they say "returned with feedback"? > ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage > 30.60. Does anyone know what that one is about? Maybe something like > MATCH_RECOGNIZE in Oracle? > > [1] http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&development=on > > -- > Thomas Munro > http://www.enterprisedb.com
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Stefan Keller
Date:
Hi Peter, hi all, I wrote 2016-07-06 13:19 GMT+02:00: > ... > 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: >> ... But ISO/IEC CD 9075-15 >> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >> Working Group". Is that how they say "returned with feedback"? >> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >> 30.60. Does anyone know what that one is about? ... @Peter B. Can you clarify the stage? Regarding the differences of array functions of the ISO proposal, I have no clue why the working group did not follow the implementation of PostgreSQL. @Peter B. You know PostgreSQL well. Can you explain? :Stefan P.S. 2016-07-02 17:11 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter E. had observer status at one point, don't know if he still does. @Peter E.: Do you still have observer status at the ISO committee? 2016-07-06 13:19 GMT+02:00 Stefan Keller <sfkeller@gmail.com>: > Thomas > > 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: >> ... But ISO/IEC CD 9075-15 >> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >> Working Group". Is that how they say "returned with feedback"? >> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >> 30.60. Does anyone know what that one is about? Maybe something like > > Peter surely would know: https://www.jacobs-university.de/directory/pbaumann > > :Stefan > > 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: >> On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote: >>> Hi, >>> >>> FYI: I'd just like to point you to following two forthcoming standard >>> parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on >>> "Multi-Dimensional Arrays" (SQL/MDA). >>> >>> They define there some things different as already in PG. See also >>> Peter Baumann's slides [1] and e.g. [2] >>> >>> :Stefan >>> >>> [1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql >>> [2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf >> >> Thanks for these pointers. On the "standards under development" >> page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60 >> "Close of voting/ comment period". But ISO/IEC CD 9075-15 >> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >> Working Group". Is that how they say "returned with feedback"? >> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >> 30.60. Does anyone know what that one is about? Maybe something like >> MATCH_RECOGNIZE in Oracle? >> >> [1] http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&development=on >> >> -- >> Thomas Munro >> http://www.enterprisedb.com
Re: Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
From
Peter Baumann
Date:
Hi Stefan, On 07/09/2016 04:03 PM, Stefan Keller wrote: > Hi Peter, hi all, > > I wrote 2016-07-06 13:19 GMT+02:00: >> ... >> 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: >>> ... But ISO/IEC CD 9075-15 >>> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >>> Working Group". Is that how they say "returned with feedback"? >>> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >>> 30.60. Does anyone know what that one is about? ... > @Peter B. Can you clarify the stage? there were comments from the national delegations which have to be worked through by providing a draft that implements changes where considered applicable, and justifying when not implemented. This is in accordance with ISO "bureaucracy", if you will, which defines a fine-grain, therefore transparent, process. Personally, I find this many-eyes principle very fruitful. > Regarding the differences of array functions of the ISO proposal, I > have no clue why the working group did not follow the implementation > of PostgreSQL. > > @Peter B. You know PostgreSQL well. Can you explain? the WG has discussed all relevant approaches and decided in favour of the one it found most suitable. cheers, Peter > > :Stefan > > P.S. 2016-07-02 17:11 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Peter E. had observer status at one point, don't know if he still does. > @Peter E.: Do you still have observer status at the ISO committee? > > > > 2016-07-06 13:19 GMT+02:00 Stefan Keller <sfkeller@gmail.com>: >> Thomas >> >> 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: >>> ... But ISO/IEC CD 9075-15 >>> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >>> Working Group". Is that how they say "returned with feedback"? >>> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >>> 30.60. Does anyone know what that one is about? Maybe something like >> Peter surely would know: https://www.jacobs-university.de/directory/pbaumann >> >> :Stefan >> >> 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>: >>> On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote: >>>> Hi, >>>> >>>> FYI: I'd just like to point you to following two forthcoming standard >>>> parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on >>>> "Multi-Dimensional Arrays" (SQL/MDA). >>>> >>>> They define there some things different as already in PG. See also >>>> Peter Baumann's slides [1] and e.g. [2] >>>> >>>> :Stefan >>>> >>>> [1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql >>>> [2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf >>> Thanks for these pointers. On the "standards under development" >>> page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60 >>> "Close of voting/ comment period". But ISO/IEC CD 9075-15 >>> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >>> Working Group". Is that how they say "returned with feedback"? >>> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >>> 30.60. Does anyone know what that one is about? Maybe something like >>> MATCH_RECOGNIZE in Oracle? >>> >>> [1] http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&development=on >>> >>> -- >>> Thomas Munro >>> http://www.enterprisedb.com -- Dr. Peter Baumann- Professor of Computer Science, Jacobs University Bremen www.faculty.jacobs-university.de/pbaumann mail:p.baumann@jacobs-university.de tel: +49-421-200-3178, fax: +49-421-200-493178- Executive Director, rasdaman GmbH Bremen(HRB 26793) www.rasdaman.com, mail: baumann@rasdaman.com tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882 "Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec preripiat quisquam non sibi parata." (mail disclaimer, AD 1083)