[sqlite] Feedback request: JSON support in SQLite
Domingo Alvarez Duarte wrote: > it seems that the json* functions ignore the collation completely, > is that by design or is it a forgotten implementation ? The JSON functions use JSON rules. RFC 7159 section 8.3 does not mention collations. Regards, Clemens
[sqlite] Feedback request: JSON support in SQLite
Hello !
Looking at the documentation of json* functions and after see the example
given for a query on a field mixed owith string and json_array:
___ SELECT name FROM user WHERE phone LIKE '704-%' UNION SELECT
user.nameFROM user, json_each(user.phone) WHERE json_valid(user.phone)
AND json_each.value LIKE '704-%';
___
That sparked a question that also relates with the new "index over
expressions" given the example above would be a natural need to index on json
arrays.
pseudo sql
CREATE TABLE user(name, phones);
CREATE INDEX user_phones ON user(json_array_each_one(phones));
INSERT INTO user VALUES('Paul', '["12345678","87654324", "5302513"]');
SELECT * FROM USER WHERE json_array_each_one(phones) = '87654321';
___
On the pseudo example above I was expecting the creation of an index with
zero or more entries for each record to allow quick searches.
Cheers !
?
> Fri Sep 11 2015 5:58:36 pm CEST CEST from "Richard Hipp"
>Subject: [sqlite] Feedback request: JSON support in SQLite
>
> Draft documentation for the current design of JSON support in SQLite
> can be seen on-line at
>
> https://www.sqlite.org/draft/json1.html
>
> Your feedback is encouraged.
>
> All features described in the document above are implemented and
> working in the latest trunk version of SQLite, which you can download
> in source-code form from https://www.sqlite.org/src/timeline?y=ci and
> compile yourself using instructions found at
> https://www.sqlite.org/src/doc/trunk/README.md
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] Feedback request: JSON support in SQLite
Hello !
I did some tests to see how the json* functions behave when we specify
collation to the column that stores it and it seems that the json* functions
ignore the collation completely, is that by design or is it a forgotten
implementation ?
___
CREATE TABLE a(j collate nocase);
INSERT INTO a values('"blue"'), ('"Blue"'), ('{"a":"blue","B":"BLUE"}');
SELECT 'select *',* FROM a;
SELECT 'j = ''"bLue"''',* FROM a WHERE j = '"bLue"';
SELECT 'j LIKE ''%bLue%''',* FROM a WHERE j LIKE '%bLue%';
SELECT 'json_extract(j, ''$'')', json_extract(j, '$') AS jstr FROM a;
SELECT json_extract(j, '$') AS jstr FROM a WHERE jstr = 'bLue'; -- I was
expecting collation to propagate here
SELECT json_extract(j, '$.a') AS jstr FROM a;
SELECT json_extract(j, '$.b') AS jstr FROM a; -- I was expecting $.B to match
$.b due to collation nocase here
___
Cheers !
> Fri Sep 11 2015 5:58:36 pm CEST CEST from "Richard Hipp"
>Subject: [sqlite] Feedback request: JSON support in SQLite
>
> Draft documentation for the current design of JSON support in SQLite
> can be seen on-line at
>
> https://www.sqlite.org/draft/json1.html
>
> Your feedback is encouraged.
>
> All features described in the document above are implemented and
> working in the latest trunk version of SQLite, which you can download
> in source-code form from https://www.sqlite.org/src/timeline?y=ci and
> compile yourself using instructions found at
> https://www.sqlite.org/src/doc/trunk/README.md
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] Feedback request: JSON support in SQLite
Hello !
That's what json_extract does !
json_extract('[2,3,4,5,6]', '$[3]'); --> returns 5;
Cheers !
> Fri Sep 11 2015 8:40:36 pm CEST CEST from "Nelson, Erik - 2"
> Subject: Re: [sqlite] Feedback request:
>JSON support in SQLite
>
> Richard Hipp wrote on Friday, September 11, 2015 11:59 AM
>
>
>>Draft documentation for the current design of JSON support in SQLite
>> can be seen on-line at
>>
>> https://www.sqlite.org/draft/json1.html
>>
> This looks really good!
>
> With the understanding that json_extract() already has the functionality,
>I've found that the json_array_* functions can be complemented by a
>json_array_index() function that returns the value at a given index into the
>array.
>
> Erik
>
> --
> This message, and any attachments, is for the intended recipient(s) only,
>may contain information that is privileged, confidential and/or proprietary
>and subject to important terms and conditions available at
>http://www.bankofamerica.com/emaildisclaimer. If you are not the intended
>recipient, please delete this message.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] Feedback request: JSON support in SQLite
On 2015-09-11 07:50 PM, Keith Christian wrote: > Pardon me, but: At what point does the code required for the > inclusion of a multitude of supported data formats exceed the core > purpose of the executable? > > At some point, obtaining a desired output format (from the potentially > dozens available) might be offloaded to a different executable that > performs that function, rather than bloating program X with a thousand > input/output formats. Well, it /IS/ a loadable extension - i.e. only for those who want it. It isn't changing the core really. https://www.sqlite.org/draft/loadext.html
[sqlite] Feedback request: JSON support in SQLite
Hello ! I'm looking at these new extensions/functions and noticed that from scripting languages to be able to use sqlite3_value_subtype we'll need access to sqlite3_context parameters by index, because from the scripting language once we are called there is no easy way from the script to refer to the original sqlite3_value. Would be possible to also have: int sqlite3_context_argc(sqlite3_context *ctx); //return the number of parameters when called through this context sqlite3_value *sqlite3_context_argv(sqlite3_context *ctx, int argIdx); //return the argIdx sqlite3_value parameter from the sqlite3_context ? The data to be returned by these two new functions is alread on the sqlite3_context structure. Cheers ! ? > > > > Fri Sep 11 2015 5:58:36 pm CEST CEST from "Richard Hipp" >Subject: [sqlite] Feedback request: JSON support in SQLite > > Draft documentation for the current design of JSON support in SQLite > can be seen on-line at > > https://www.sqlite.org/draft/json1.html > > Your feedback is encouraged. > > All features described in the document above are implemented and > working in the latest trunk version of SQLite, which you can download > in source-code form from https://www.sqlite.org/src/timeline?y=ci and > compile yourself using instructions found at > https://www.sqlite.org/src/doc/trunk/README.md > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Feedback request: JSON support in SQLite
On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp wrote: > Your feedback is encouraged. > https://www.sqlite.org/draft/c3ref/value_subtype.html does not say what happens or which value we get, should one call sqlite3_value_subtype on a sqlite_value* which which no sqlite3_result_subtype() was made. Can we have some guarantee, like always 0, or MAX_INT, or something? Thanks, --DD PS: Thanks for this json1 experimental extension!
[sqlite] Feedback request: JSON support in SQLite
On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp wrote: > Draft documentation for the current design of JSON support in SQLite [...] Your feedback is encouraged. > "Experiments have so far been unable to find a binary encoding that is significantly smaller or faster than a plain text encoding" Have you had a look at or tried CBOR Richard? Thanks, --DD http://cbor.io/ http://tools.ietf.org/html/rfc7049
[sqlite] Feedback request: JSON support in SQLite
> On Sep 11, 2015, at 6:31 PM, Richard Hipp wrote: > > a passing fad On the other hand, we can now embrace NormalFormZero without undue embarrassment. Swell.
[sqlite] Feedback request: JSON support in SQLite
Richard Hipp wrote on Friday, September 11, 2015 11:59 AM > > Draft documentation for the current design of JSON support in SQLite > can be seen on-line at > > https://www.sqlite.org/draft/json1.html This looks really good! With the understanding that json_extract() already has the functionality, I've found that the json_array_* functions can be complemented by a json_array_index() function that returns the value at a given index into the array. Erik -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
[sqlite] Feedback request: JSON support in SQLite
> On Sep 11, 2015, at 5:58 PM, Richard Hipp wrote: > > JSON support in SQLite JSON, eh? No MERGE. No analytics. But serialization of the week is covered. Sweet :D
[sqlite] Feedback request: JSON support in SQLite
On Fri, Sep 11, 2015 at 8:58 AM, Richard Hipp wrote:
> Draft documentation for the current design of JSON support in SQLite
> can be seen on-line at
>
> https://www.sqlite.org/draft/json1.html
>
> Your feedback is encouraged.
>
> All features described in the document above are implemented and
> working in the latest trunk version of SQLite, which you can download
> in source-code form from https://www.sqlite.org/src/timeline?y=ci and
> compile yourself using instructions found at
> https://www.sqlite.org/src/doc/trunk/README.md
Given json_each(), you can transform json into a table-alike feature. I
found myself wondering if there was anything which could easily take a
result set and convert it to json. I can see that something like this
could convert a result row into a json object:
SELECT json_object("c", c, "d", d", ...) FROM ...;
but it's not obvious to me how to feed all of those json objects to
json_array(). You could do things like string together a bunch of
sub-queries, I suppose.
Riffing off group_concat(), maybe something like this makes sense:
SELECT json_group_array(json_object("c", c, "d", d)) FROM t WHERE
GROUP BY ;
Now, if you were to ask me what I would use that for ... I would come up
blank. I mostly suggested it because it feels like there is a piece
missing.
===break===
Someone else mentioned json_array_index() - while I agree that
json_extract() can do the same thing, it does somewhat feel like a natural
thing to have json_array_index(). And it could optionally support slicing:
json_array_index('[1,2,3,4]', 1) -> 2
json_array_index('[1,2,3,4]', 2, 2) -> '[3,4]'
Though I suppose something like:
json_extract('[1,2,3,4]', '$[0,2..3]')
might be a cleaner place to put a slicing syntax. Of course if I went
there, I'd also wonder about '$.{a,c}' as syntax to generate an object
slice. Maybe v+1 for this kind of thing.
===break===
In the table for json_insert/replace/set(), I suspect the last row should
have function json_set().
-scott
[sqlite] Feedback request: JSON support in SQLite
On 9/11/15, Dominique Devienne wrote: > On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp wrote: > >> Your feedback is encouraged. >> > > https://www.sqlite.org/draft/c3ref/value_subtype.html does not say what > happens or which value we get, > should one call sqlite3_value_subtype on a sqlite_value* which which > no sqlite3_result_subtype() > was made. > > Can we have some guarantee, like always 0, or MAX_INT, or something? Always 0. -- D. Richard Hipp drh at sqlite.org
[sqlite] Feedback request: JSON support in SQLite
On 9/11/15, Petite Abeille wrote: > > we can now embrace NormalFormZero without undue > embarrassment. Well put. :-) -- D. Richard Hipp drh at sqlite.org
[sqlite] Feedback request: JSON support in SQLite
On 9/11/15, Petite Abeille wrote: > serialization of the week The json.org website has been up since 2002. JSON itself predates that. It is roughly the same age as SQLite itself and is older than SQLite3. I'm thinking that maybe JSON is not just a passing fad. Could be wrong though. -- D. Richard Hipp drh at sqlite.org
[sqlite] Feedback request: JSON support in SQLite
Draft documentation for the current design of JSON support in SQLite can be seen on-line at https://www.sqlite.org/draft/json1.html Your feedback is encouraged. All features described in the document above are implemented and working in the latest trunk version of SQLite, which you can download in source-code form from https://www.sqlite.org/src/timeline?y=ci and compile yourself using instructions found at https://www.sqlite.org/src/doc/trunk/README.md -- D. Richard Hipp drh at sqlite.org
[sqlite] Feedback request: JSON support in SQLite
Pardon me, but: At what point does the code required for the inclusion of a multitude of supported data formats exceed the core purpose of the executable? At some point, obtaining a desired output format (from the potentially dozens available) might be offloaded to a different executable that performs that function, rather than bloating program X with a thousand input/output formats. This is an unsolicited opinion. The decision rests with the good Dr. Hipp, who selflessly volunteers his time and skills. Keith On Fri, Sep 11, 2015 at 11:26 AM, Dominique Devienne wrote: > On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp wrote: > >> Your feedback is encouraged. >> > > https://www.sqlite.org/draft/c3ref/value_subtype.html does not say what > happens or which value we get, > should one call sqlite3_value_subtype on a sqlite_value* which which > no sqlite3_result_subtype() > was made. > > Can we have some guarantee, like always 0, or MAX_INT, or something? > Thanks, --DD > > PS: Thanks for this json1 experimental extension! > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feedback request: JSON support in SQLite
On Fri, Sep 11, 2015 at 11:31 AM, Richard Hipp wrote: > On 9/11/15, Petite Abeille wrote: > > serialization of the week > > The json.org website has been up since 2002. JSON itself predates > that. It is roughly the same age as SQLite itself and is older than > SQLite3. I'm thinking that maybe JSON is not just a passing fad. > Could be wrong though. > -- > D. Richard Hipp > drh at sqlite.org > > ?PostgreSQL has had JSON for a couple of releases now. I am amazed at the number of messages on that forum about "how do I use JSON in the WHERE clause of a SELECT?" and other such where people want to not just store JSON encoded data, but do SELECTs and UPDATEs on _parts_ of a JSON encoded column. They're like polygamists in that they want their Kate and Edith too. [grin/] I.e. relational queries against a non-relational field. ? ?Oh, and the latest DB/2 UDB from IBM is getting JSON columns as well. I don't know what their implementation does with it.? -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Feedback request: JSON support in SQLite
On 2015-09-11 9:31 AM, Richard Hipp wrote: > On 9/11/15, Petite Abeille wrote: >> serialization of the week > > The json.org website has been up since 2002. JSON itself predates > that. It is roughly the same age as SQLite itself and is older than > SQLite3. I'm thinking that maybe JSON is not just a passing fad. > Could be wrong though. True, I have known for years that JSON has effectively become the new XML, one that is less verbose but evenly flexible. Referring to the spec itself, storing the JSON as a character string is always a safe bet; having a future binary option is good, but AFAIK the primary benefit to it is faster indexing/processing speed, and not so much space; see also the (BSD-licensed) Postgres 9.4+ and its JSONB data type for prior art with a binary storage of JSON that is backwards-compatible, though its indexability is one of its key features. -- Darren Duncan
