4 ms·
From a glance, this looks like its compatible with MySQL's JSON functions. It isn't compatible, for example, with MariaDB's dynamic columns. I'm not very fami
by willvarfar 8y ago
From a glance, this looks like its compatible with MySQL's JSON functions.
It isn't compatible, for example, with MariaDB's dynamic columns.
I'm not very familiar with the PostgreSQL json functionality, but I think that's subtly different again.
When I use local dbs for testing my json code I've used derby and defined the json_extract() functions etc myself just to test. With Sqlite having compatible functions, people wanting to test mysql stuff locally will be able to just point their code at an Sqlite DB instead. Great!
The bit that seems to be missing is the shorthand for selecting column values; in MySQL, instead of doing SELECT JSON_EXTRACT(col, "$.this.is.ugly[12]"), ... you can just do SELECT col->"$.this.is.ugly[12]", ...
Now what I want to be able to write is SELECT col.this.is.nicer[12], ....
I think there is some 'standard' somewhere that MySQL - and now Sqlite - is implementing? The functions and the 'path' syntax are standardized (although with only MySQL and now Sqlite supporting them its not perhaps a big deal). I just can't find any reference to that standard in the MySQL docs, nor this Sqlite doc.
Personally, I dislike the path syntax though! Every time I see an sql snippet with string paths full of dollar signs it offends my retinas.
- tannhaeuser 8y agoAccording to [1], ISO/IEC 9075:2016 specifies JSON functionality in ISO SQL, though I don't have access to it/didn't buy the standard text. I also don't know whether Postgres etc. is designed to follow the standard, as Postgres' implementation predates it, but would expect sqlite to. [1]: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016 https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
- masklinn 8y agoPostgres 11 doesn't follow the standard at all (as per your link). A third party implemented the standard [0] which was submitted on pghackers[1] very late in the Postgres 11 cycle[2] (at 75k gzipped the patch is pretty humongous). A quick googling doesn't show any information pertaining it being integrated (in part or full) into pg12 so IDK. edit: apparently https://commitfest.postgresql.org/17/1471/ https://commitfest.postgresql.org/17/1471/ has been "Waiting on Author" and bumped from CF to CF since early 2018, and https://commitfest.postgresql.org/17/1472/ https://commitfest.postgresql.org/17/1472/ and https://commitfest.postgresql.org/17/1473/ https://commitfest.postgresql.org/17/1473/ pretty much the same with no "waiting on author" but I don't really know how CF works and I see no comment or requests or reviews so… [0] https://obartunov.livejournal.com/200076.html https://obartunov.livejournal.com/200076.html [1] https://www.postgresql.org/message-id/CAF4Au4w2x-5LTnN_bxky-mq4=WOqsGsxSpENCzHRAzSnEd8+WQ@mail.gmail.com https://www.postgresql.org/message-id/CAF4Au4w2x-5LTnN_bxky-... [2] https://www.postgresql.org/message-id/00531c7e-f501-b852-9b67-1d1278d035a0%40pgmasters.net https://www.postgresql.org/message-id/00531c7e-f501-b852-9b6...
- anarazel 8y agoThey're actively working on it. There's been a new revisions posted over the last few days, responding to review from a week or two ago. https://www.postgresql.org/message-id/a3be6a7a-77d3-0e88-4f9d-4f725d11d7cd%40postgrespro.ru https://www.postgresql.org/message-id/a3be6a7a-77d3-0e88-4f9... https://www.postgresql.org/message-id/c2f32c9f-9a69-202b-a8aa-d93c769a579e%40postgrespro.ru https://www.postgresql.org/message-id/c2f32c9f-9a69-202b-a8a... > I see no comment or requests or reviews so… That happens on the mailing list...
- misev 8y agoThere's a technical report ISO/IEC TR 19075-6:2017 corresponding to the standard, freely available from [1]. It's a much more accessible version of the standard if you're not looking to implement it. 1. https://standards.iso.org/ittf/PubliclyAvailableStandards/ https://standards.iso.org/ittf/PubliclyAvailableStandards/
- paulsutter 8y agoThe json sql syntax was probably invented by the same jagoffs who came up with C++ STL map api. I can’t imagine how much bikeshedding it took to come up with this crap.