CONNECT JSON Table Type Overview JSON (JavaScript Object Notation) is a lightweight data-interchange format widely used on the Internet. Many applications, generally written in JavaScript or PHP use and produce JSON data, which are exchanged as files of different physical formats. JSON data is often returned from REST queries. It is also possible to query, create or update such information in a database-like manner. MongoDB does it using a JavaScript-like language. PostgreSQL includes these facilities by using a specific data type and related functions like dynamic columns. The CONNECT engine adds this facility to MariaDB by supporting tables based on JSON data files. This is done like for XML tables by creating tables describing what should be retrieved from the file and how it should be processed. Starting with 1.07.0002, the internal way JSON was parsed and handled was changed. The main advantage of the new way is to reduce the memory required to parse JSON. It was from 6 to 10 times the size of the JSON source and is now only 2 to 4 times. However, this is in Beta mode and JSON tables are still handled using the old mode. To use the new mode, tables should be created with TABLE_TYPE=BSON. Another way is the set the connect_force_bson session variable to 1 or ON. Then all JSON tables will be handled as BSON. Of course, this is temporary and when successfully tested, the new way will replace the old way and all tables be created as JSON. Let us start from the file “biblio3.json” that is the JSON equivalent of the XML Xsample file described in the XML table chapter: [ { "ISBN": "9782212090819", "LANG": "fr", "SUBJECT": "applications", "AUTHOR": [ { "FIRSTNAME": "Jean-Christophe", "LASTNAME": "Bernadac" }, { "FIRSTNAME": "François", "LASTNAME": "Knab" } ], "TITLE": "Construire une application XML", "PUBLISHER": { "NAME": "Eyrolles", "PLACE": "Paris" }, "DATEPUB": 1999 }, { "ISBN": "9782840825685", "LANG": "fr", "SUBJECT": "applications", "AUTHOR": [ { "FIRSTNAME": "William J.", "LASTNAME": "Pardi" } ], "TITLE": "XML en Action", "TRANSLATED": { "PREFIX": "adapté de l'anglais par", "TRANSLATOR": { "FIRSTNAME": "James", "LASTNAME": "Guerin" } }, "PUBLISHER": { "NAME": "Microsoft Press", "PLACE": "Paris" }, "DATEPUB": 1999 } ] This file contains the different items existing in JSON. Arrays: They are enclosed in square brackets and contain a list of comma separated values. Objects: They are enclosed in curly brackets. They contain a comma separated list of pairs, each pair composed of a key name between double quotes, followed by a ‘:’ character and followed by a value. Values: Values can be an array or an object. They also can be a string between double quotes, an integer or float number, a Boolean value or a null value. The simplest way for CONNECT to locate a table in such a file is by an array containing a list of objects (this is what MongoDB calls a collection of documents). Each array value will be a table row and each pair of the row objects will represent a column, the key being the column name and the value the column value. A first try to create a table on this file will be to take the outer array as the table: create table jsample ( ISBN char(15), LANG char(2), SUBJECT char(32), AUTHOR char(128), TITLE char(32), TRANSLATED char(80), PUBLISHER char(20), DATEPUB int(4)) engine=CONNECT table_type=JSON File_name='biblio3.json'; If we execute the query: select isbn, author, title, publisher from jsample; We get the result: isbn author title publisher 9782212090819 Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 9782840825685 William J. Pardi XML en Action Microsoft Press Pari Note that by default, column values that are objects have been set to the concatenation of all the string values of the object separated by a blank. When a column value is an array, only the first item of the array is retrieved (This will change in later versions of Connect). However, things are generally more complicated. If JSON files do not contain attributes (although object pairs are similar to attributes) they contain a new item, arrays. We have seen that they can be used like XML multiple nodes, here to specify several authors, but they are more general because they can contain objects of different types, even it may not be advisable to do so. This is why CONNECT enables the specification of a column field_format option “JPATH” (FIELD_FORMAT until Connect 1.6) that is used to describe exactly where the items to display are and how to handles arrays. Here is an example of a new table that can be created on the same file, allowing choosing the column names, to get some sub-objects and to specify how to handle the author array. Until Connect 1.5: create table jsampall ( ISBN char(15), Language char(2) field_format='LANG', Subject char(32) field_format='SUBJECT', Author char(128) field_format='AUTHOR:[" and "]', Title char(32) field_format='TITLE', Translation char(32) field_format='TRANSLATOR:PREFIX', Translator char(80) field_format='TRANSLATOR', Publisher char(20) field_format='PUBLISHER:NAME', Location char(16) field_format='PUBLISHER:PLACE', Year int(4) field_format='DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json'; From Connect 1.6: create table jsampall ( ISBN char(15), Language char(2) field_format='LANG', Subject char(32) field_format='SUBJECT', Author char(128) field_format='AUTHOR.[" and "]', Title char(32) field_format='TITLE', Translation char(32) field_format='TRANSLATOR.PREFIX', Translator char(80) field_format='TRANSLATOR', Publisher char(20) field_format='PUBLISHER.NAME', Location char(16) field_format='PUBLISHER.PLACE', Year int(4) field_format='DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json'; From Connect 1.07.0002 create table jsampall ( ISBN char(15), Language char(2) jpath='$.LANG', Subject char(32) jpath='$.SUBJECT', Author char(128) jpath='$.AUTHOR[" and "]', Title char(32) jpath='$.TITLE', Translation char(32) jpath='$.TRANSLATOR.PREFIX', Translator char(80) jpath='$.TRANSLATOR', Publisher char(20) jpath='$.PUBLISHER.NAME', Location char(16) jpath='$.PUBLISHER.PLACE', Year int(4) jpath='$.DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json'; Given the query: select title, author, publisher, location from jsampall; The result is: title author publisher location Construire une application XML Jean-Christophe Bernadac and François Knab Eyrolles Paris XML en Action William J. Pardi Microsoft Press Paris Note: The JPATH was not specified for column ISBN because it defaults to the column name. Here is another example showing that one can choose what to extract from the file and how to “expand” an array, meaning to generate one row for each array value: Until Connect 1.5: create table jsampex ( ISBN char(15), Title char(32) field_format='TITLE', AuthorFN char(128) field_format='AUTHOR:[X]:FIRSTNAME', AuthorLN char(128) field_format='AUTHOR:[X]:LASTNAME', Year int(4) field_format='DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json'; From Connect 1.6: create table jsampex ( ISBN char(15), Title char(32) field_format='TITLE', AuthorFN char(128) field_format='AUTHOR.[X].FIRSTNAME', AuthorLN char(128) field_format='AUTHOR.[X].LASTNAME', Year int(4) field_format='DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json'; From Connect 1.06.006: create table jsampex ( ISBN char(15), Title char(32) field_format='TITLE', AuthorFN char(128) field_format='AUTHOR[*].FIRSTNAME', AuthorLN char(128) field_format='AUTHOR[*].LASTNAME', Year int(4) field_format='DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json'; From Connect 1.07.0002 create table jsampex ( ISBN char(15), Title char(32) jpath='TITLE', AuthorFN char(128) jpath='AUTHOR[*].FIRSTNAME', AuthorLN char(128) jpath='AUTHOR[*].LASTNAME', Year int(4) jpath='DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json'; It is displayed as: ISBN Title AuthorFN AuthorLN Year 9782212090819 Construire une application XML Jean-Christophe Bernadac 1999 9782212090819 Construire une application XML François Knab 1999 9782840825685 XML en Action William J. Pardi 1999 Note: The example above shows that the ‘$.’, that means the beginning of the path, can be omitted. The Jpath Specification From Connect 1.6, the Jpath specification has changed to be the one of the native JSON functions and more compatible with what is generally used. It is close to the standard definition and compatible to what MongoDB and other products do. The ‘:’ separator is replaced by ‘.’. Position in array is accepted MongoDB style with no square brackets. Array specification specific to CONNECT are still accepted but [*] is used for expanding and [x] for multiply. However, tables created with the previous syntax can still be used by adding SEP_CHAR=’:’ (can be done with alter table). Also, it can be now specified as JPATH (was FIELD_FORMAT) but FIELD_FORMAT is still accepted. Until Connect 1.5, it is the description of the path to follow to reach the required item. Each step is the key name (case sensitive) of the pair when crossing an object, and the number of the value between square brackets when crossing an array. Each specification is separated by a ‘:’ character. From Connect 1.6, It is the description of the path to follow to reach the required item. Each step is the key name (case sensitive) of the pair when crossing an object, and the position number of the value when crossing an array. Key specifications are separated by a ‘.’ character. For instance, in the above file, the last name of the second author of a book is reached by: $.AUTHOR[1].LASTNAME standard style $AUTHOR.1.LASTNAME MongoDB style AUTHOR:[1]:LASTNAME old style when SEP_CHAR=’:’ or until Connect 1.5 The ‘$’ or “$.” prefix specifies the root of the path and can be omitted with CONNECT. The array specification can also indicate how it must be processed: For instance, in the above file, the last name of the second author of a book is reached by: AUTHOR:[1]:LASTNAME The array specification can also indicate how it must be processed: Specification Array Type Limit Description n (Connect >= 1.6) or [n][1] All N.A Take the nth value of the array. [*] (Connect >= 1.6), [X] or [x] (Connect <= 1.5) All Expand. Generate one row for each array value. ["string"] String Concatenate all values separated by the specified string. [+] Numeric Make the sum of all the non-null array values. [x] (Connect >= 1.6), [*] (Connect <= 1.5) Numeric Make the product of all non-null array values. [!] Numeric Make the average of all the non-null array values. [>] or [<] All Return the greatest or least non-null value of the array. [#] All N.A Return the number of values in the array. [] All Expand if under an expanded object. Otherwise sum if numeric, else concatenation separated by “, “. All Between two separators, if an array, expand it if under an expanded object or take the first value of it. Note 1: When the LIMIT restriction is applicable, only the first m array items are used, m being the value of the LIMIT option (to be specified in option_list). The LIMIT default value is 10. Note 2: An alternative way to indicate what is to be expanded is to use the expand option in the option list, for instance: OPTION_LIST='Expand=AUTHOR' AUTHOR is here the key of the pair that has the array as a value (case sensitive). Expand is limited to only one branch (expanded arrays must be under the same object). Let us take as an example the file expense.json (found here). The table jexpall expands all under and including the week array: From Connect 1.07.0002 create table jexpall ( WHO char(12), WEEK int(2) jpath='$.WEEK[*].NUMBER', WHAT char(32) jpath='$.WEEK[*].EXPENSE[*].WHAT', AMOUNT double(8,2) jpath='$.WEEK[*].EXPENSE[*].AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; From Connect.1.6 create table jexpall ( WHO char(12), WEEK int(2) field_format='$.WEEK[*].NUMBER', WHAT char(32) field_format='$.WEEK[*].EXPENSE[*].WHAT', AMOUNT double(8,2) field_format='$.WEEK[*].EXPENSE[*].AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; Until Connect 1.5: create table jexpall ( WHO char(12), WEEK int(2) field_format='WEEK:[x]:NUMBER', WHAT char(32) field_format='WEEK:[x]:EXPENSE:[x]:WHAT', AMOUNT double(8,2) field_format='WEEK:[x]:EXPENSE:[x]:AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 Joe 3 Food 12.00 Joe 3 Food 19.00 Joe 3 Car 20.00 Joe 4 Beer 19.00 Joe 4 Beer 16.00 Joe 4 Food 17.00 Joe 4 Food 17.00 Joe 4 Beer 14.00 Joe 5 Beer 14.00 Joe 5 Food 12.00 Beth 3 Beer 16.00 Beth 4 Food 17.00 Beth 4 Beer 15.00 Beth 5 Food 12.00 Beth 5 Beer 20.00 Janet 3 Car 19.00 Janet 3 Food 18.00 Janet 3 Beer 18.00 Janet 4 Car 17.00 Janet 5 Beer 14.00 Janet 5 Car 12.00 Janet 5 Beer 19.00 Janet 5 Food 12.00 The table jexpw shows what was bought and the sum and average of amounts for each person and week: From Connect 1.07.0002 create table jexpw ( WHO char(12) not null, WEEK int(2) not null jpath='$.WEEK[*].NUMBER', WHAT char(32) not null jpath='$.WEEK[].EXPENSE[", "].WHAT', SUM double(8,2) not null jpath='$.WEEK[].EXPENSE[+].AMOUNT', AVERAGE double(8,2) not null jpath='$.WEEK[].EXPENSE[!].AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; From Connect 1.6: create table jexpw ( WHO char(12) not null, WEEK int(2) not null field_format='$.WEEK[*].NUMBER', WHAT char(32) not null field_format='$.WEEK[].EXPENSE[", "].WHAT', SUM double(8,2) not null field_format='$.WEEK[].EXPENSE[+].AMOUNT', AVERAGE double(8,2) not null field_format='$.WEEK[].EXPENSE[!].AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; Until Connect 1.5: create table jexpw ( WHO char(12) not null, WEEK int(2) not null field_format='WEEK:[x]:NUMBER', WHAT char(32) not null field_format='WEEK8b7c:f320:99b9:690f:4595:cd17:293a:c069XPENSE:[", "]:WHAT', SUM double(8,2) not null field_format='WEEK8b7c:f320:99b9:690f:4595:cd17:293a:c069XPENSE:[+]:AMOUNT', AVERAGE double(8,2) not null field_format='WEEK8b7c:f320:99b9:690f:4595:cd17:293a:c069XPENSE:[!]:AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; WHO WEEK WHAT SUM AVERAGE Joe 3 Beer, Food, Food, Car 69.00 17.25 Joe 4 Beer, Beer, Food, Food, Beer 83.00 16.60 Joe 5 Beer, Food 26.00 13.00 Beth 3 Beer 16.00 16.00 Beth 4 Food, Beer 32.00 16.00 Beth 5 Food, Beer 32.00 16.00 Janet 3 Car, Food, Beer 55.00 18.33 Janet 4 Car 17.00 17.00 Janet 5 Beer, Car, Beer, Food 57.00 14.25 Let us see what the table jexpz does: From Connect 1.6: create table jexpz ( WHO char(12) not null, WEEKS char(12) not null field_format='WEEK[", "].NUMBER', SUMS char(64) not null field_format='WEEK["+"].EXPENSE[+].AMOUNT', SUM double(8,2) not null field_format='WEEK[+].EXPENSE[+].AMOUNT', AVGS char(64) not null field_format='WEEK["+"].EXPENSE[!].AMOUNT', SUMAVG double(8,2) not null field_format='WEEK[+].EXPENSE[!].AMOUNT', AVGSUM double(8,2) not null field_format='WEEK[!].EXPENSE[+].AMOUNT', AVERAGE double(8,2) not null field_format='WEEK[!].EXPENSE[*].AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; From Connect 1.07.0002 create table jexpz ( WHO char(12) not null, WEEKS char(12) not null jpath='WEEK[", "].NUMBER', SUMS char(64) not null jpath='WEEK["+"].EXPENSE[+].AMOUNT', SUM double(8,2) not null jpath='WEEK[+].EXPENSE[+].AMOUNT', AVGS char(64) not null jpath='WEEK["+"].EXPENSE[!].AMOUNT', SUMAVG double(8,2) not null jpath='WEEK[+].EXPENSE[!].AMOUNT', AVGSUM double(8,2) not null jpath='WEEK[!].EXPENSE[+].AMOUNT', AVERAGE double(8,2) not null jpath='WEEK[!].EXPENSE[*].AMOUNT') engine=CONNECT table_type=JSON File_name='expense.json'; Until Connect 1.5: create table jexpz ( WHO char(12) not null, WEEKS char(12) not null field_format='WEEK:[", "]:NUMBER', SUMS char(64) not null field_format='WEEK:["+"]:EXPENSE:[+]:AMOUNT', SUM double(8,2) not null field_format='WEEK:[+]:EXPENSE:[+]:AMOUNT', AVGS char(64) not null field_format='WEEK:["+"]:EXPENSE:[!]:AMOUNT', SUMAVG double(8,2) not null field_format='WEEK:[+]:EXPENSE:[!]:AMOUNT', AVGSUM double(8,2) not null field_format='WEEK:[!]:EXPENSE:[+]:AMOUNT', AVERAGE double(8,2) not null field_format='WEEK:[!]:EXPENSE:[x]:AMOUNT') engine=CONNECT table_type=JSON File_name='E:/Data/Json/expense2.json'; WHO WEEKS SUMS SUM AVGS SUMAVG AVGSUM AVERAGE Joe 3, 4, 5 69.00+83.00+26.00 178.00 17.25+16.60+13.00 46.85 59.33 16.18 Beth 3, 4, 5 16.00+32.00+32.00 80.00 16.00+16.00+16.00 48.00 26.67 16.00 Janet 3, 4, 5 55.00+17.00+57.00 129.00 18.33+17.00+14.25 49.58 43.00 16.12 For all persons: Column 1 show the person name. Column 2 shows the weeks for which values are calculated. Column 3 lists the sums of expenses for each week. Column 4 calculates the sum of all expenses by person. Column 5 shows the week’s expense averages. Column 6 calculates the sum of these averages. Column 7 calculates the average of the week’s sum of expenses. Column 8 calculates the average expense by person. It would be very difficult, if even possible, to obtain this result from table jexpall using an SQL query. Handling of NULL Values Json has a null explicit value that can be met in arrays or object key values. When regarding json as a relational table, a column value can be null because the corresponding json item is explicitly null, or implicitly because the corresponding item is missing in an array or object. CONNECT does not make any distinction between explicit and implicit nulls. However, it is possible to specify how nulls are handled and represented. This is done by setting the string session variable connect_json_null. The default value of connect_json_null is “<null>”; it can be changed, for instance, by: SET connect_json_null='NULL'; This changes its representation when a column displays the text of an object or the concatenation of the values of an array. It is also possible to tell CONNECT to ignore nulls by: SET connect_json_null=NULL; When doing so, nulls do not appear in object text or array lists. However, this does not change the behavior of array calculation nor the result of array count. Having Columns defined by Discovery It is possible to let the MariaDB discovery process do the job of column specification. When columns are not defined in the create table statement, CONNECT endeavors to analyze the JSON file and to provide the column specifications. This is possible only for tables represented by an array of objects because CONNECT retrieves the column names from the object pair keys and their definition from the object pair values. For instance, the jsample table could be created saying: create table jsample engine=connect table_type=JSON file_name='biblio3.json'; Let’s check how it was actually specified using the show create table statement: CREATE TABLE `jsample` ( `ISBN` char(13) NOT NULL, `LANG` char(2) NOT NULL, `SUBJECT` char(12) NOT NULL, `AUTHOR` varchar(256) DEFAULT NULL, `TITLE` char(30) NOT NULL, `TRANSLATED` varchar(256) DEFAULT NULL, `PUBLISHER` varchar(256) DEFAULT NULL, `DATEPUB` int(4) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' `FILE_NAME`='biblio3.json'; It is equivalent except for the column sizes that have been calculated from the file as the maximum length of the corresponding column when it was a normal value. For columns that are json arrays or objects, the column is specified as a varchar string of length 256, supposedly big enough to contain the sub-object's concatenated values. Nullable is set to true if the column is null or missing in some rows or if its JPATH contains arrays. If a more complex definition is desired, you can ask CONNECT to analyse the JPATH up to a given depth using the DEPTH or LEVEL option in the option list. Its default value is 0 but can be changed setting the connect_default_depth session variable (in future versions the default will be 5). The depth value is the number of sub-objects that are taken in the JPATH2 (this is different from what is defined and returned by the native Json_Depth function). For instance: create table jsampall2 engine=connect table_type=JSON file_name='biblio3.json' option_list='level=1'; This will define the table as: From Connect 1.07.0002 CREATE TABLE `jsampall2` ( `ISBN` char(13) NOT NULL, `LANG` char(2) NOT NULL, `SUBJECT` char(12) NOT NULL, `AUTHOR_FIRSTNAME` char(15) NOT NULL `JPATH`='$.AUTHOR.[0].FIRSTNAME', `AUTHOR_LASTNAME` char(8) NOT NULL `JPATH`='$.AUTHOR.[0].LASTNAME', `TITLE` char(30) NOT NULL, `TRANSLATED_PREFIX` char(23) DEFAULT NULL `JPATH`='$.TRANSLATED.PREFIX', `TRANSLATED_TRANSLATOR` varchar(256) DEFAULT NULL `JPATH`='$.TRANSLATED.TRANSLATOR', `PUBLISHER_NAME` char(15) NOT NULL `JPATH`='$.PUBLISHER.NAME', `PUBLISHER_PLACE` char(5) NOT NULL `JPATH`='$.PUBLISHER.PLACE', `DATEPUB` int(4) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' `FILE_NAME`='biblio3.json' `OPTION_LIST`='depth=1'; From Connect 1.6: CREATE TABLE `jsampall2` ( `ISBN` char(13) NOT NULL, `LANG` char(2) NOT NULL, `SUBJECT` char(12) NOT NULL, `AUTHOR_FIRSTNAME` char(15) NOT NULL `FIELD_FORMAT`='AUTHOR..FIRSTNAME', `AUTHOR_LASTNAME` char(8) NOT NULL `FIELD_FORMAT`='AUTHOR..LASTNAME', `TITLE` char(30) NOT NULL, `TRANSLATED_PREFIX` char(23) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED.PREFIX', `TRANSLATED_TRANSLATOR` varchar(256) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED.TRANSLATOR', `PUBLISHER_NAME` char(15) NOT NULL `FIELD_FORMAT`='PUBLISHER.NAME', `PUBLISHER_PLACE` char(5) NOT NULL `FIELD_FORMAT`='PUBLISHER.PLACE', `DATEPUB` int(4) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' `FILE_NAME`='biblio3.json' `OPTION_LIST`='level=1'; Until Connect 1.5: CREATE TABLE `jsampall2` ( `ISBN` char(13) NOT NULL, `LANG` char(2) NOT NULL, `SUBJECT` char(12) NOT NULL, `AUTHOR_FIRSTNAME` char(15) NOT NULL `FIELD_FORMAT`='AUTHOR8b7c:f320:99b9:690f:4595:cd17:293a:c069IRSTNAME', `AUTHOR_LASTNAME` char(8) NOT NULL `FIELD_FORMAT`='AUTHOR8b7c:f320:99b9:690f:4595:cd17:293a:c069LASTNAME', `TITLE` char(30) NOT NULL, `TRANSLATED_PREFIX` char(23) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED:PREFIX', `TRANSLATED_TRANSLATOR` varchar(256) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED:TRANSLATOR', `PUBLISHER_NAME` char(15) NOT NULL `FIELD_FORMAT`='PUBLISHER:NAME', `PUBLISHER_PLACE` char(5) NOT NULL `FIELD_FORMAT`='PUBLISHER:PLACE', `DATEPUB` int(4) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' ` FILE_NAME`='biblio3.json' `OPTION_LIST`='level=1'; For columns that are a simple value, the Json path is the column name. This is the default when the Jpath option is not specified, so it was not specified for such columns. However, you can force discovery to specify it by setting the connect_all_path variable to 1 or ON. This can be useful if you plan to change the name of such columns and relieves you of manually specifying the path (otherwise it would default to the new name and cause the column to not or wrongly be found). Another problem is that CONNECT cannot guess what you want to do with arrays. Here the AUTHOR array is set to 0, which means that only its first value will be retrieved unless you also had specified “Expand=AUTHOR” in the option list. But of course, you can replace it with anything else. This method can be used as a quick way to make a “template” table definition that can later be edited to make the desired definition. In particular, column names are constructed from all the object keys of their path in order to have distinct column names. This can be manually edited to have the desired names, provided their JPATH key names are not modified. DEPTH can also be given the value -1 to create only columns that are simple values (no array or object). It normally defaults to 0 but this can be modified setting the connect_default_depth variable. Note: Since version 1.6.4, CONNECT eliminates columns that are “void” or whose type cannot be determined. For instance given the file sresto.json: {"_id":1,"name":"Corner Social","cuisine":"American","grades":[{"grade":"A","score":6}]} {"_id":2,"name":"La Nueva Clasica Antillana","cuisine":"Spanish","grades":[]} Previously, when using discovery, creating the table by: create table sjr0 engine=connect table_type=JSON file_name='sresto.json' option_list='Pretty=0,Depth=1' lrecl=128; The table was previously created as: CREATE TABLE `sjr0` ( `_id` bigint(1) NOT NULL, `name` char(26) NOT NULL, `cuisine` char(8) NOT NULL, `grades` char(1) DEFAULT NULL, `grades_grade` char(1) DEFAULT NULL `JPATH`='$.grades[0].grade', `grades_score` bigint(1) DEFAULT NULL `JPATH`='$.grades[0].score' ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' `FILE_NAME`='sresto.json' `OPTION_LIST`='Pretty=0,Depth=1,Accept=1' `LRECL`=128; The column “grades” was added because of the void array in line 2. Now this column is skipped and does not appear anymore (unless the option Accept=1 is added in the option list). JSON Catalogue Tables Another way to see JSON table column specifications is to use a catalogue table. For instance: create table bibcol engine=connect table_type=JSON file_name='biblio3.json' option_list='level=2' catfunc=columns; select column_name, type_name type, column_size size, jpath from bibcol; which returns: From Connect 1.07.0002: column_name type size jpath ISBN CHAR 13 $.ISBN LANG CHAR 2 $.LANG SUBJECT CHAR 12 $.SUBJECT AUTHOR_FIRSTNAME CHAR 15 $.AUTHOR[0].FIRSTNAME AUTHOR_LASTNAME CHAR 8 $.AUTHOR[0].LASTNAME TITLE CHAR 30 $.TITLE TRANSLATED_PREFIX CHAR 23 $.TRANSLATED.PREFIX TRANSLATED_TRANSLATOR_FIRSTNAME CHAR 5 $TRANSLATED.TRANSLATOR.FIRSTNAME TRANSLATED_TRANSLATOR_LASTNAME CHAR 6 $.TRANSLATED.TRANSLATOR.LASTNAME PUBLISHER_NAME CHAR 15 $.PUBLISHER.NAME PUBLISHER_PLACE CHAR 5 $.PUBLISHER.PLACE DATEPUB INTEGER 4 $.DATEPUB From Connect 1.6: column_name type size jpath ISBN CHAR 13 LANG CHAR 2 SUBJECT CHAR 12 AUTHOR_FIRSTNAME CHAR 15 AUTHOR..FIRSTNAME AUTHOR_LASTNAME CHAR 8 AUTHOR..LASTNAME TITLE CHAR 30 TRANSLATED_PREFIX CHAR 23 TRANSLATED.PREFIX TRANSLATED_TRANSLATOR_FIRSTNAME CHAR 5 TRANSLATED.TRANSLATOR.FIRSTNAME TRANSLATED_TRANSLATOR_LASTNAME CHAR 6 TRANSLATED.TRANSLATOR.LASTNAME PUBLISHER_NAME CHAR 15 PUBLISHER.NAME PUBLISHER_PLACE CHAR 5 PUBLISHER.PLACE DATEPUB INTEGER 4 Until Connect 1.5: column_name type size jpath ISBN CHAR 13 LANG CHAR 2 SUBJECT CHAR 12 AUTHOR_FIRSTNAME CHAR 15 AUTHOR8b7c:f320:99b9:690f:4595:cd17:293a:c069IRSTNAME AUTHOR_LASTNAME CHAR 8 AUTHOR8b7c:f320:99b9:690f:4595:cd17:293a:c069LASTNAME TITLE CHAR 30 TRANSLATED_PREFIX CHAR 23 TRANSLATED:PREFIX TRANSLATED_TRANSLATOR_FIRSTNAME CHAR 5 TRANSLATED:TRANSLATOR:FIRSTNAME TRANSLATED_TRANSLATOR_LASTNAME CHAR 6 TRANSLATED:TRANSLATOR:LASTNAME PUBLISHER_NAME CHAR 15 PUBLISHER:NAME PUBLISHER_PLACE CHAR 5 PUBLISHER:PLACE DATEPUB INTEGER 4 All this is mostly useful when creating a table on a remote file that you cannot easily see. Finding the table within a JSON file Given the file “facebook.json”: { "data": [ { "id": "X999_Y999", "from": { "name": "Tom Brady", "id": "X12" }, "message": "Looking forward to 2010!", "actions": [ { "name": "Comment", "link": "http://www.facebook.com/X999/posts/Y999" }, { "name": "Like", "link": "http://www.facebook.com/X999/posts/Y999" } ], "type": "status", "created_time": "2010-08-02T21:27:44+0000", "updated_time": "2010-08-02T21:27:44+0000" }, { "id": "X998_Y998", "from": { "name": "Peyton Manning", "id": "X18" }, "message": "Where's my contract?", "actions": [ { "name": "Comment", "link": "http://www.facebook.com/X998/posts/Y998" }, { "name": "Like", "link": "http://www.facebook.com/X998/posts/Y998" } ], "type": "status", "created_time": "2010-08-02T21:27:44+0000", "updated_time": "2010-08-02T21:27:44+0000" } ] } The table we want to analyze is represented by the array value of the “data” object. Here is how this is specified in the create table statement: From Connect 1.07.0002: create table jfacebook ( `ID` char(10) jpath='id', `Name` char(32) jpath='from.name', `MyID` char(16) jpath='from.id', `Message` varchar(256) jpath='message', `Action` char(16) jpath='actions..name', `Link` varchar(256) jpath='actions..link', `Type` char(16) jpath='type', `Created` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' jpath='created_time', `Updated` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' jpath='updated_time') engine=connect table_type=JSON file_name='facebook.json' option_list='Object=data,Expand=actions'; From Connect 1.6: create table jfacebook ( `ID` char(10) field_format='id', `Name` char(32) field_format='from.name', `MyID` char(16) field_format='from.id', `Message` varchar(256) field_format='message', `Action` char(16) field_format='actions..name', `Link` varchar(256) field_format='actions..link', `Type` char(16) field_format='type', `Created` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='created_time', `Updated` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='updated_time') engine=connect table_type=JSON file_name='facebook.json' option_list='Object=data,Expand=actions'; Until Connect 1.5: create table jfacebook ( `ID` char(10) field_format='id', `Name` char(32) field_format='from:name', `MyID` char(16) field_format='from:id', `Message` varchar(256) field_format='message', `Action` char(16) field_format='actions8b7c:f320:99b9:690f:4595:cd17:293a:c069name', `Link` varchar(256) field_format='actions8b7c:f320:99b9:690f:4595:cd17:293a:c069link', `Type` char(16) field_format='type', `Created` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='created_time', `Updated` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='updated_time') engine=connect table_type=JSON file_name='facebook.json' option_list='Object=data,Expand=actions'; This is the object option that gives the Jpath of the table. Note also an alternate way to declare the array to be expanded by the expand option of the option_list. Because some string values contain a date representation, the corresponding columns are declared as datetime and the date format is specified for them. The Jpath of the object option has the same syntax as the column Jpath but of course all array steps must be specified using the [n] (until Connect 1.5) or n (from Connect 1.6) format. Note: This applies to the whole document for tables having PRETTY = 2 (see below). Otherwise, it applies to the document objects of each file records. JSON File Formats The examples we have seen so far are files that, even they can be formatted in different ways (blanks, tabs, carriage return and line feed are ignored when parsing them), respect the JSON syntax and are made of only one item (Object or Array). Like for XML files, they are entirely parsed and a memory representation is made used to process them. This implies that they are of reasonable size to avoid an out of memory condition. Tables based on such files are recognized by the option Pretty=2 that we did not specify above because this is the default. An alternate format, which is the format of exported MongoDB files, is a file where each row is physically stored in one file record. For instance: { "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" } { "_id" : "01002", "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA" } { "_id" : "01005", "city" : "BARRE", "loc" : [ -72.1083540000001, 42.409698 ], "pop" : 4546, "state" : "MA" } { "_id" : "01007", "city" : "BELCHERTOWN", "loc" : [ -72.4109530000001, 42.275103 ], "pop" : 10579, "state" : "MA" } … { "_id" : "99929", "city" : "WRANGELL", "loc" : [ -132.352918, 56.433524 ], "pop" : 2573, "state" : "AK" } { "_id" : "99950", "city" : "KETCHIKAN", "loc" : [ -133.18479, 55.942471 ], "pop" : 422, "state" : "AK" } The original file, “cities.json”, has 29352 records. To base a table on this file we must specify the option Pretty=0 in the option list. For instance: From Connect 1.07.0002: create table cities ( `_id` char(5) key, `city` char(32), `lat` double(12,6) jpath='loc.0', `long` double(12,6) jpath='loc.1', `pop` int(8), `state` char(2) distrib='clustered') engine=CONNECT table_type=JSON file_name='cities.json' lrecl=128 option_list='pretty=0'; From Connect 1.6: create table cities ( `_id` char(5) key, `city` char(32), `lat` double(12,6) field_format='loc.0', `long` double(12,6) field_format='loc.1', `pop` int(8), `state` char(2) distrib='clustered') engine=CONNECT table_type=JSON file_name='cities.json' lrecl=128 option_list='pretty=0'; Until Connect 1.5: create table cities ( `_id` char(5) key, `city` char(32), `long` double(12,6) field_format='loc:[0]', `lat` double(12,6) field_format='loc:[1]', `pop` int(8), `state` char(2) distrib='clustered') engine=CONNECT table_type=JSON file_name='cities.json' lrecl=128 option_list='pretty=0'; Note the use of [n] (until Connect 1.5) or n (from Connect 1.6) array specifications for the longitude and latitude columns. When using this format, the table is processed by CONNECT like a DOS, CSV or FMT table. Rows are retrieved and parsed by records and the table can be very large. Another advantage is that such a table can be indexed, which can be of great value for very large tables. The “distrib” option of the “state” column tells CONNECT to use block indexing when possible. For such tables – as well as for pretty=1 ones – the record size must be specified using the LRECL option. Be sure you don’t specify it too small as it is used to allocate the read/write buffers and the memory used for parsing the rows. If in doubt, be generous as it does not cost much in memory allocation. Another format exists, noted by Pretty=1, which is similar to this one but has some additions to represent a JSON array. A header and a trailer records are added containing the opening and closing square bracket, and all records but the last are followed by a comma. It has the same advantages for reading and updating, but inserting and deleting are executed in the pretty=2 way. Alternate Table Arrangement We have seen that the most natural way to represent a table in a JSON file is to make it on an array of objects. However, other possibilities exist. A table can be an array of arrays, a one column table can be an array of values, or a one row table can be just one object or one value. Single row tables are internally handled by adding a one value array around them. Let us see how to handle, for instance, a table that is an array of arrays. The file: [ [56, "Coucou", 500.00], [[2,0,1,4], "Hello World", 2.0316], ["1784", "John Doo", 32.4500], [1914, ["Nabucho","donosor"], 5.12], [7, "sept", [0.77,1.22,2.01]], [8, "huit", 13.0] ] A table can be created on this file as: From Connect 1.07.0002: create table xjson ( `a` int(6) jpath='1', `b` char(32) jpath='2', `c` double(10,4) jpath='3') engine=connect table_type=JSON file_name='test.json' option_list='Pretty=1,Jmode=1,Base=1' lrecl=128; From Connect 1.6: create table xjson ( `a` int(6) field_format='1', `b` char(32) field_format='2', `c` double(10,4) field_format='3') engine=connect table_type=JSON file_name='test.json' option_list='Pretty=1,Jmode=1,Base=1' lrecl=128; Until Connect 1.5: create table xjson ( `a` int(6) field_format='[1]', `b` char(32) field_format='[2]', `c` double(10,4) field_format='[3]') engine=connect table_type=JSON file_name='test.json' option_list='Pretty=1,Jmode=1,Base=1' lrecl=128; Columns are specified by their position in the row arrays. By default, this is zero-based but for this table the base was set to 1 by the Base option of the option list. Another new option in the option list is Jmode=1. It indicates what type of table this is. The Jmode values are: An array of objects. This is the default. An array of Array. Like this one. An array of values. When reading, this is not required as the type of the array items is specified for the columns; however, it is required when inserting new rows so CONNECT knows what to insert. For instance: insert into xjson values(25, 'Breakfast', 1.414); After this, it is displayed as: a b c 56 Coucou 500.0000 2 Hello World 2.0316 1784 John Doo 32.4500 1914 Nabucho 5.1200 7 sept 0.7700 8 huit 13.0000 25 Breakfast 1.4140 Unspecified array values are represented by their first element. Getting and Setting JSON Representation of a Column We have seen that columns corresponding to a Json object or array are retrieved by default as the concatenation of all its values separated by a blank. It is also possible to retrieve and display such column contains as the full JSON string corresponding to it in the JSON file. This is specified in the JPATH by a “*” where the object or array would be specified. Note: When having columns generated by discovery, this can be specified by adding the STRINGIFY option to ON or 1 in the option list. For instance: From Connect 1.07.0002: create table jsample2 ( ISBN char(15), Lng char(2) jpath='LANG', json_Author char(255) jpath='AUTHOR.*', Title char(32) jpath='TITLE', Year int(4) jpath='DATEPUB') engine=CONNECT table_type=JSON file_name='biblio3.json'; From Connect 1.6: create table jsample2 ( ISBN char(15), Lng char(2) field_format='LANG', json_Author char(255) field_format='AUTHOR.*', Title char(32) field_format='TITLE', Year int(4) field_format='DATEPUB') engine=CONNECT table_type=JSON file_name='biblio3.json'; Until Connect 1.5: create table jsample2 ( ISBN char(15), Lng char(2) field_format='LANG', json_Author char(255) field_format='AUTHOR:*', Title char(32) field_format='TITLE', Year int(4) field_format='DATEPUB') engine=CONNECT table_type=JSON file_name='biblio3.json'; Now the query: select json_Author from jsample2; will return and display : json_Author [{"FIRSTNAME":"Jean-Christophe","LASTNAME":"Bernadac"},{"FIRSTNAME":"François","LASTNAME":"Knab"}] [{"FIRSTNAME":"William J.","LASTNAME":"Pardi"}] Note: Prefixing the column name by json_ is optional but is useful when using the column as argument to Connect UDF functions, making it to be surely recognized as valid Json without aliasing. This also works on input, a column specified so that it can be directly set to a valid JSON string. This feature is of great value as we will see below. Create, Read, Update and Delete Operations on JSON Tables The SQL commands INSERT, UPDATE and DELETE are fully supported for JSON tables except those returned by REST queries. For INSERT and UPDATE, if the target values are simple values, there are no problems. However, there are some issues when the added or modified values are objects or arrays. Concerning objects, the same problems exist that we have already seen with the XML type. The added or modified object will have the format described in the table definition, which can be different from the one of the JSON file. Modifications should be done using a file specifying the full path of modified objects. New problems are raised when trying to modify the values of an array. Only updates can be done on the original table. First of all, for the values of the array to be distinct values, all update operations concerning array values must be done using a table expanding this array. For instance, to modify the authors of the biblio.json based table, the jsampex table must be used. Doing so, updating and deleting authors is possible using standard SQL commands. For example, to change the first name of Knab from François to John: update jsampex set authorfn = 'John' where authorln = 'Knab'; However It would be wrong to do: update jsampex set authorfn = 'John' where isbn = '9782212090819'; Because this would change the first name of both authors as they share the same ISBN. Where things become more difficult is when trying to delete or insert an author of a book. Indeed, a delete command will delete the whole book and an insert command will add a new complete row instead of adding a new author in the same array. Here we are penalized by the SQL language that cannot give us a way to specify this. Something like: update jsampex add authorfn = 'Charles', authorln = 'Dickens' where title = 'XML en Action'; However this does not exist in SQL. Does this mean that it is impossible to do it? No, but it requires us to use a table specified on the same file but adapted to this task. One way to do it is to specify a table for which the authors are no more an expanded array. Supposing we want to add an author to the “XML en Action” book. We will do it on a table containing just the author(s) of that book, which is the second book of the table. From Connect 1.6: create table jauthor ( FIRSTNAME char(64), LASTNAME char(64)) engine=CONNECT table_type=JSON File_name='biblio3.json' option_list='Object=1.AUTHOR'; Until Connect 1.5 create table jauthor ( FIRSTNAME char(64), LASTNAME char(64)) engine=CONNECT table_type=JSON File_name='biblio3.json' option_list='Object=[1]:AUTHOR'; The command: select * from jauthor; replies: FIRSTNAME LASTNAME William J. Pardi It is a standard JSON table that is an array of objects in which we can freely insert or delete rows. insert into jauthor values('Charles','Dickens'); We can check that this was done correctly by: select * from jsampex; This will display: ISBN Title AuthorFN AuthorLN Year 9782212090819 Construire une application XML Jean-Christophe Bernadac 1999 9782212090819 Construire une application XML John Knab 1999 9782840825685 XML en Action William J. Pardi 1999 9782840825685 XML en Action Charles Dickens 1999 Note: If this table were a big table with many books, it would be difficult to know what the order of a specific book is in the table. This can be found by adding a special ROWID column in the table. However, an alternate way to do it is by using direct JSON column representation as in the JSAMPLE2 table. This can be done by: update jsample2 set json_Author = '[{"FIRSTNAME":"William J.","LASTNAME":"Pardi"}, {"FIRSTNAME":"Charles","LASTNAME":"Dickens"}]' where isbn = '9782840825685'; Here, we didn't have to find the index of the sub array to modify. However, this is not quite satisfying because we had to manually write the whole JSON value to set to the json_Author column. Therefore we need specific functions to do so. They are introduced now. JSON User Defined Functions Although such functions written by other parties do exist,[2] CONNECT provides its own UDFs that are specifically adapted to the JSON table type and easily available because, being inside the CONNECT library or DLL, they require no additional module to be loaded (see CONNECT - Compiling JSON UDFs in a Separate Library to make these functions in a separate library module). In particular, MariaDB 10.2 and 10.3 feature native JSON functions. In some cases, it is possible that these native functions can be used. However, mixing native and UDF JSON functions in the same query often does not work because the way they recognize their arguments is different and might even cause a server crash. Here is the list of the CONNECT functions; more can be added if required. Name Type Return Description Added jbin_array Function STRING* Make a JSON array containing its arguments. MariaDB 10.1.9 jbin_array_add Function STRING* Adds to its first array argument its second arguments. MariaDB 10.1.9 jbin_array_add_values Function STRING* Adds to its first array argument all following arguments. jbin_array_delete Function STRING* Deletes the nth element of its first array argument. MariaDB 10.1.9 jbin_file Function STRING* Returns of a (json) file contain. MariaDB 10.1.9 jbin_get_item Function STRING* Access and returns a json item by a JPATH key. MariaDB 10.1.9 jbin_insert_item Function STRING Insert item values located to paths. jbin_item_merge Function STRING* Merges two arrays or two objects. MariaDB 10.1.9 jbin_object Function STRING* Make a JSON object containing its arguments. MariaDB 10.1.9 jbin_object_nonull Function STRING* Make a JSON object containing its not null arguments. MariaDB 10.1.9 jbin_object_add Function STRING* Adds to its first object argument its second argument. MariaDB 10.1.9 jbin_object_delete Function STRING* Deletes the nth element of its first object argument. MariaDB 10.1.9 jbin_object_key Function STRING* Make a JSON object for key/value pairs. jbin_object_list Function STRING* Returns the list of object keys as an array. MariaDB 10.1.9 jbin_set_item Function STRING Set item values located to paths. jbin_update_item Function STRING Update item values located to paths. jfile_bjson Function STRING Convert a pretty=0 file to another BJson file. MariaDB 10.5.9, MariaDB 10.4.18, MariaDB 10.3.28, MariaDB 10.2.36 jfile_convert Function STRING Convert a Json file to another pretty=0 file. MariaDB 10.5.9, MariaDB 10.4.18, MariaDB 10.3.28, MariaDB 10.2.36 jfile_make Function STRING Make a json file from its json item first argument. MariaDB 10.1.9 json_array Function STRING Make a JSON array containing its arguments. MariaDB 10.0.17 until Connect 1.5 json_array_add Function STRING Adds to its first array argument its second arguments (before MariaDB 10.1.9, all following arguments). json_array_add_values Function STRING Adds to its first array argument all following arguments. MariaDB 10.1.9 json_array_delete Function STRING Deletes the nth element of its first array argument. json_array_grp Aggregate STRING Makes JSON arrays from coming argument. json_file Function STRING Returns the contains of (json) file. MariaDB 10.1.9 json_get_item Function STRING Access and returns a json item by a JPATH key. MariaDB 10.1.9 json_insert_item Function STRING Insert item values located to paths. json_item_merge Function STRING Merges two arrays or two objects. MariaDB 10.1.9 json_locate_all Function STRING Returns the JPATH’s of all occurrences of an element. MariaDB 10.1.9 json_make_array Function STRING Make a JSON array containing its arguments. From Connect 1.6 json_make_object Function STRING Make a JSON object containing its arguments. From Connect 1.6 json_object Function STRING Make a JSON object containing its arguments. MariaDB 10.0.17 until Connect 1.5 json_object_delete Function STRING Deletes the nth element of its first object argument. MariaDB 10.1.9 json_object_grp Aggregate STRING Makes JSON objects from coming arguments. json_object_list Function STRING Returns the list of object keys as an array. MariaDB 10.1.9 json_object_nonull Function STRING Make a JSON object containing its not null arguments. json_serialize Function STRING Serializes the return of a “Jbin” function. MariaDB 10.1.9 json_set_item Function STRING Set item values located to paths. json_update_item Function STRING Update item values located to paths. jsonvalue Function STRING Make a JSON value from its unique argument. Called json_value until MariaDB 10.0.22 and MariaDB 10.1.8. MariaDB 10.0.17 jsoncontains Function INTEGER Returns 0 or 1 if an element is contained in the document. jsoncontains_path Function INTEGER Returns 0 or 1 if a JPATH is contained in the document. jsonget_string Function STRING Access and returns a string element by a JPATH key. MariaDB 10.1.9 jsonget_int Function INTEGER Access and returns an integer element by a JPATH key. MariaDB 10.1.9 jsonget_real Function REAL Access and returns a real element by a JPATH key. MariaDB 10.1.9 jsonlocate Function STRING Returns the JPATH to access one element. MariaDB 10.1.9 String values are mapped to JSON strings. These strings are automatically escaped to conform to the JSON syntax. The automatic escaping is bypassed when the value has an alias beginning with ‘json_’. This is automatically the case when a JSON UDF argument is another JSON UDF whose name begins with “json_” (not case sensitive). This is why all functions that do not return a Json item are not prefixed by “json_”. Argument string values, for some functions, can alternatively be json file names. When this is ambiguous, alias them as jfile_. Full path should be used because UDF functions has no means to know what the current database is. Apparently, when the file name path is not full, it is based on the MariaDB data directory but I am not sure it is always true. Numeric values are (big) integers, double floating point values or decimal values. Decimal values are character strings containing a numeric representation and are treated as strings. Floating point values contain a decimal point and/or an exponent. Integers are written without decimal points. To install these functions execute the following commands :[3] Note: Json function names are often written on this page with leading upper case letters for clarity. It is possible to do so in SQL queries because function names are case insensitive. However, when creating or dropping them, their names must match the case they are in the library module (lower case from MariaDB 10.1.9). On Unix systems (from Connect 1.7.02): create function jsonvalue returns string soname 'ha_connect.so'; create function json_make_array returns string soname 'ha_connect.so'; create function json_array_add_values returns string soname 'ha_connect.so'; create function json_array_add returns string soname 'ha_connect.so'; create function json_array_delete returns string soname 'ha_connect.so'; create function json_make_object returns string soname 'ha_connect.so'; create function json_object_nonull returns string soname 'ha_connect.so'; create function json_object_key returns string soname 'ha_connect.so'; create function json_object_add returns string soname 'ha_connect.so'; create function json_object_delete returns string soname 'ha_connect.so'; create function json_object_list returns string soname 'ha_connect.so'; create function json_object_values returns string soname 'ha_connect.so'; create function jsonset_grp_size returns integer soname 'ha_connect.so'; create function jsonget_grp_size returns integer soname 'ha_connect.so'; create aggregate function json_array_grp returns string soname 'ha_connect.so'; create aggregate function json_object_grp returns string soname 'ha_connect.so'; create function jsonlocate returns string soname 'ha_connect.so'; create function json_locate_all returns string soname 'ha_connect.so'; create function jsoncontains returns integer soname 'ha_connect.so'; create function jsoncontains_path returns integer soname 'ha_connect.so'; create function json_item_merge returns string soname 'ha_connect.so'; create function json_get_item returns string soname 'ha_connect.so'; create function jsonget_string returns string soname 'ha_connect.so'; create function jsonget_int returns integer soname 'ha_connect.so'; create function jsonget_real returns real soname 'ha_connect.so'; create function json_set_item returns string soname 'ha_connect.so'; create function json_insert_item returns string soname 'ha_connect.so'; create function json_update_item returns string soname 'ha_connect.so'; create function json_file returns string soname 'ha_connect.so'; create function jfile_make returns string soname 'ha_connect.so'; create function jfile_convert returns string soname 'ha_connect.so'; create function jfile_bjson returns string soname 'ha_connect.so'; create function json_serialize returns string soname 'ha_connect.so'; create function jbin_array returns string soname 'ha_connect.so'; create function jbin_array_add_values returns string soname 'ha_connect.so'; create function jbin_array_add returns string soname 'ha_connect.so'; create function jbin_array_delete returns string soname 'ha_connect.so'; create function jbin_object returns string soname 'ha_connect.so'; create function jbin_object_nonull returns string soname 'ha_connect.so'; create function jbin_object_key returns string soname 'ha_connect.so'; create function jbin_object_add returns string soname 'ha_connect.so'; create function jbin_object_delete returns string soname 'ha_connect.so'; create function jbin_object_list returns string soname 'ha_connect.so'; create function jbin_item_merge returns string soname 'ha_connect.so'; create function jbin_get_item returns string soname 'ha_connect.so'; create function jbin_set_item returns string soname 'ha_connect.so'; create function jbin_insert_item returns string soname 'ha_connect.so'; create function jbin_update_item returns string soname 'ha_connect.so'; create function jbin_file returns string soname 'ha_connect.so'; On Unix systems (from Connect 1.6): create function jsonvalue returns string soname 'ha_connect.so'; create function json_make_array returns string soname 'ha_connect.so'; create function json_array_add_values returns string soname 'ha_connect.so'; create function json_array_add returns string soname 'ha_connect.so'; create function json_array_delete returns string soname 'ha_connect.so'; create function json_make_object returns string soname 'ha_connect.so'; create function json_object_nonull returns string soname 'ha_connect.so'; create function json_object_key returns string soname 'ha_connect.so'; create function json_object_add returns string soname 'ha_connect.so'; create function json_object_delete returns string soname 'ha_connect.so'; create function json_object_list returns string soname 'ha_connect.so'; create function jsonset_grp_size returns integer soname 'ha_connect.so'; create function jsonget_grp_size returns integer soname 'ha_connect.so'; create aggregate function json_array_grp returns string soname 'ha_connect.so'; create aggregate function json_object_grp returns string soname 'ha_connect.so'; create function jsonlocate returns string soname 'ha_connect.so'; create function json_locate_all returns string soname 'ha_connect.so'; create function jsoncontains returns integer soname 'ha_connect.so'; create function jsoncontains_path returns integer soname 'ha_connect.so'; create function json_item_merge returns string soname 'ha_connect.so'; create function json_get_item returns string soname 'ha_connect.so'; create function jsonget_string returns string soname 'ha_connect.so'; create function jsonget_int returns integer soname 'ha_connect.so'; create function jsonget_real returns real soname 'ha_connect.so'; create function json_set_item returns string soname 'ha_connect.so'; create function json_insert_item returns string soname 'ha_connect.so'; create function json_update_item returns string soname 'ha_connect.so'; create function json_file returns string soname 'ha_connect.so'; create function jfile_make returns string soname 'ha_connect.so'; create function json_serialize returns string soname 'ha_connect.so'; create function jbin_array returns string soname 'ha_connect.so'; create function jbin_array_add_values returns string soname 'ha_connect.so'; create function jbin_array_add returns string soname 'ha_connect.so'; create function jbin_array_delete returns string soname 'ha_connect.so'; create function jbin_object returns string soname 'ha_connect.so'; create function jbin_object_nonull returns string soname 'ha_connect.so'; create function jbin_object_key returns string soname 'ha_connect.so'; create function jbin_object_add returns string soname 'ha_connect.so'; create function jbin_object_delete returns string soname 'ha_connect.so'; create function jbin_object_list returns string soname 'ha_connect.so'; create function jbin_item_merge returns string soname 'ha_connect.so'; create function jbin_get_item returns string soname 'ha_connect.so'; create function jbin_set_item returns string soname 'ha_connect.so'; create function jbin_insert_item returns string soname 'ha_connect.so'; create function jbin_update_item returns string soname 'ha_connect.so'; create function jbin_file returns string soname 'ha_connect.so'; On Unix systems (from MariaDB 10.1.9 until Connect 1.5): create function jsonvalue returns string soname 'ha_connect.so'; create function json_array returns string soname 'ha_connect.so'; create function json_array_add_values returns string soname 'ha_connect.so'; create function json_array_add returns string soname 'ha_connect.so'; create function json_array_delete returns string soname 'ha_connect.so'; create function json_object returns string soname 'ha_connect.so'; create function json_object_nonull returns string soname 'ha_connect.so'; create function json_object_key returns string soname 'ha_connect.so'; create function json_object_add returns string soname 'ha_connect.so'; create function json_object_delete returns string soname 'ha_connect.so'; create function json_object_list returns string soname 'ha_connect.so'; create function jsonset_grp_size returns integer soname 'ha_connect.so'; create function jsonget_grp_size returns integer soname 'ha_connect.so'; create aggregate function json_array_grp returns string soname 'ha_connect.so'; create aggregate function json_object_grp returns string soname 'ha_connect.so'; create function jsonlocate returns string soname 'ha_connect.so'; create function json_locate_all returns string soname 'ha_connect.so'; create function jsoncontains returns integer soname 'ha_connect.so'; create function jsoncontains_path returns integer soname 'ha_connect.so'; create function json_item_merge returns string soname 'ha_connect.so'; create function json_get_item returns string soname 'ha_connect.so'; create function jsonget_string returns string soname 'ha_connect.so'; create function jsonget_int returns integer soname 'ha_connect.so'; create function jsonget_real returns real soname 'ha_connect.so'; create function json_set_item returns string soname 'ha_connect.so'; create function json_insert_item returns string soname 'ha_connect.so'; create function json_update_item returns string soname 'ha_connect.so'; create function json_file returns string soname 'ha_connect.so'; create function jfile_make returns string soname 'ha_connect.so'; create function json_serialize returns string soname 'ha_connect.so'; create function jbin_array returns string soname 'ha_connect.so'; create function jbin_array_add_values returns string soname 'ha_connect.so'; create function jbin_array_add returns string soname 'ha_connect.so'; create function jbin_array_delete returns string soname 'ha_connect.so'; create function jbin_object returns string soname 'ha_connect.so'; create function jbin_object_nonull returns string soname 'ha_connect.so'; create function jbin_object_key returns string soname 'ha_connect.so'; create function jbin_object_add returns string soname 'ha_connect.so'; create function jbin_object_delete returns string soname 'ha_connect.so'; create function jbin_object_list returns string soname 'ha_connect.so'; create function jbin_item_merge returns string soname 'ha_connect.so'; create function jbin_get_item returns string soname 'ha_connect.so'; create function jbin_set_item returns string soname 'ha_connect.so'; create function jbin_insert_item returns string soname 'ha_connect.so'; create function jbin_update_item returns string soname 'ha_connect.so'; create function jbin_file returns string soname 'ha_connect.so'; On WIndows (from Connect 1.7.02): create function jsonvalue returns string soname 'ha_connect'; create function json_make_array returns string soname 'ha_connect'; create function json_array_add_values returns string soname 'ha_connect'; create function json_array_add returns string soname 'ha_connect'; create function json_array_delete returns string soname 'ha_connect'; create function json_make_object returns string soname 'ha_connect'; create function json_object_nonull returns string soname 'ha_connect'; create function json_object_key returns string soname 'ha_connect'; create function json_object_add returns string soname 'ha_connect'; create function json_object_delete returns string soname 'ha_connect'; create function json_object_list returns string soname 'ha_connect'; create function json_object_values returns string soname 'ha_connect'; create function jsonset_grp_size returns integer soname 'ha_connect'; create function jsonget_grp_size returns integer soname 'ha_connect'; create aggregate function json_array_grp returns string soname 'ha_connect'; create aggregate function json_object_grp returns string soname 'ha_connect'; create function jsonlocate returns string soname 'ha_connect'; create function json_locate_all returns string soname 'ha_connect'; create function jsoncontains returns integer soname 'ha_connect'; create function jsoncontains_path returns integer soname 'ha_connect'; create function json_item_merge returns string soname 'ha_connect'; create function json_get_item returns string soname 'ha_connect'; create function jsonget_string returns string soname 'ha_connect'; create function jsonget_int returns integer soname 'ha_connect'; create function jsonget_real returns real soname 'ha_connect'; create function json_set_item returns string soname 'ha_connect'; create function json_insert_item returns string soname 'ha_connect'; create function json_update_item returns string soname 'ha_connect'; create function json_file returns string soname 'ha_connect'; create function jfile_make returns string soname 'ha_connect'; create function jfile_convert returns string soname 'ha_connect'; create function jfile_bjson returns string soname 'ha_connect'; create function json_serialize returns string soname 'ha_connect'; create function jbin_array returns string soname 'ha_connect'; create function jbin_array_add_values returns string soname 'ha_connect'; create function jbin_array_add returns string soname 'ha_connect'; create function jbin_array_delete returns string soname 'ha_connect'; create function jbin_object returns string soname 'ha_connect'; create function jbin_object_nonull returns string soname 'ha_connect'; create function jbin_object_key returns string soname 'ha_connect'; create function jbin_object_add returns string soname 'ha_connect'; create function jbin_object_delete returns string soname 'ha_connect'; create function jbin_object_list returns string soname 'ha_connect'; create function jbin_item_merge returns string soname 'ha_connect'; create function jbin_get_item returns string soname 'ha_connect'; create function jbin_set_item returns string soname 'ha_connect'; create function jbin_insert_item returns string soname 'ha_connect'; create function jbin_update_item returns string soname 'ha_connect'; create function jbin_file returns string soname 'ha_connect'; On WIndows (from Connect 1.6): create function jsonvalue returns string soname 'ha_connect'; create function json_make_array returns string soname 'ha_connect'; create function json_array_add_values returns string soname 'ha_connect'; create function json_array_add returns string soname 'ha_connect'; create function json_array_delete returns string soname 'ha_connect'; create function json_make_object returns string soname 'ha_connect'; create function json_object_nonull returns string soname 'ha_connect'; create function json_object_key returns string soname 'ha_connect'; create function json_object_add returns string soname 'ha_connect';
{'path': 'public/docs/mariadb/connect-json-table-type/index.html', 'source': 'DevDocs.IO'}