Hi All,
Does anyone know whether there is built-in indexing on Json datatype or not. I would like to keep my dynamic columns in Json but worry about indexing.
Regards, Chatchawan
There is no implicit indexing on columns of JSON datatype.
The JSON datatype is implemented as a subtype of the VARCHAR datatype, but extended with JSON specific functions. See: https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON So the JSON data is stored as a variable length string of UTF-8 characters, but only accepts valid JSON formatted data.
If you worry about speed of extracting parts of your JSON data using JSON or string functions, you should consider to store the to-be-extracted data parts in additional columns (or tables) once, and next run your queries against those pre-extracted columns (and tables). This mimics an index, which also stores data redundantly for faster access.
On 09-07-2019 01:58, Chatchawan Triperm wrote:
Hi All,
Does anyone know whether there is built-in indexing on Json datatype or not. I would like to keep my dynamic columns in Json but worry about indexing.
Regards, Chatchawan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thanks for your clarification. I will split my json to columns as suggested.
Regards, Chatchawan
On Tue, Jul 9, 2019 at 11:32 PM dinther dinther@monetdbsolutions.com wrote:
There is no implicit indexing on columns of JSON datatype.
The JSON datatype is implemented as a subtype of the VARCHAR datatype, but extended with JSON specific functions. See: https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON So the JSON data is stored as a variable length string of UTF-8 characters, but only accepts valid JSON formatted data.
If you worry about speed of extracting parts of your JSON data using JSON or string functions, you should consider to store the to-be-extracted data parts in additional columns (or tables) once, and next run your queries against those pre-extracted columns (and tables). This mimics an index, which also stores data redundantly for faster access.
On 09-07-2019 01:58, Chatchawan Triperm wrote:
Hi All,
Does anyone know whether there is built-in indexing on Json datatype or
not.
I would like to keep my dynamic columns in Json but worry about indexing.
Regards, Chatchawan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hai Chatchawan,
There is another way to load JSON into MonetDB: https://www.monetdb.org/blog/monetdbpython-loader-functions
This feature splits the JSON data into columns. But it does have very specific requirement to the structure of the input JSON data.
Regards, Jennie
On 11 Jul 2019, at 16:17, Chatchawan Triperm hurryplotter@gmail.com wrote:
Thanks for your clarification. I will split my json to columns as suggested.
Regards, Chatchawan
On Tue, Jul 9, 2019 at 11:32 PM dinther dinther@monetdbsolutions.com wrote: There is no implicit indexing on columns of JSON datatype.
The JSON datatype is implemented as a subtype of the VARCHAR datatype, but extended with JSON specific functions. See: https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON So the JSON data is stored as a variable length string of UTF-8 characters, but only accepts valid JSON formatted data.
If you worry about speed of extracting parts of your JSON data using JSON or string functions, you should consider to store the to-be-extracted data parts in additional columns (or tables) once, and next run your queries against those pre-extracted columns (and tables). This mimics an index, which also stores data redundantly for faster access.
On 09-07-2019 01:58, Chatchawan Triperm wrote:
Hi All,
Does anyone know whether there is built-in indexing on Json datatype or not. I would like to keep my dynamic columns in Json but worry about indexing.
Regards, Chatchawan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
There is no implicit indexing on columns of JSON datatype.
The JSON datatype is implemented as a subtype of the VARCHAR datatype, but extended with JSON specific functions. See: https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON So the JSON data is stored as a variable length string of UTF-8 characters, but only accepts valid JSON formatted data.
If you worry about speed of extracting parts of your JSON data using JSON or string functions, you should consider to store the to-be-extracted data parts in additional columns (or tables) once, and next run your queries against those pre-extracted columns (and tables). This mimics an index, which also stores data redundantly for faster access.
On 09-07-2019 01:58, Chatchawan Triperm wrote:
Hi All,
Does anyone know whether there is built-in indexing on Json datatype or not. I would like to keep my dynamic columns in Json but worry about indexing.
Regards, Chatchawan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Chatchawan Triperm -
dinther -
Ying Zhang