MySQL Forums - Document Store [X Dev API, Shell, JSON]

JSON Storage Size Issue (no replies)

Preetham KV — Wed, 23 Oct 2024 08:30:34 +0000

Hello.

We are using the json column to store some data in the table. For a particular row when we checked the JSON_STORAGE_SIZE( table.json_colum) the size used is 280KB. But the plain text json data size is just 1kb and also when we checked the SELECT JSON_STORAGE_SIZE('JSON_data'), the size required was 1.5kb.

Can someone explain why the table consumes a lot of storage size? What can we do to fix this?

Please let me know if needed any more information.

Thanks

Introducing JSON/Relational Duality for MySQL REST Service (no replies)

Edwin Desouza — Thu, 19 Oct 2023 19:10:52 +0000

Introducing JSON/Relational Duality for MySQL REST Service
- https://blogs.oracle.com/mysql/post/introducing-jsonrelational-duality-for-mysql-rest-service

I have a problem about that troubles me (no replies)

lmssee T — Sat, 13 May 2023 10:39:50 +0000

if i use " select ord from dababaseName.tableName order by ord" ,i can get this result "1 2 3 4 5 6 7".
but ,when i use "select json_arrayagg(b.ord) from (select * from databaseName.tableName as a order by a.ord) as b order by d.ord" .i only get this result "[1,2,3,4,6,7,5]".

i use "order by a.ord" in this select ,but why i can't get true order .

who can save me, i have this trouble

Introducing the MySQL REST Service (no replies)

Edwin Desouza — Wed, 15 Feb 2023 22:38:21 +0000

Introducing the MySQL REST Service:
- https://blogs.oracle.com/mysql/post/introducing-the-mysql-rest-service

SQLite `json_tree` equivalent (no replies)

Hamish Allan — Thu, 09 Feb 2023 17:10:01 +0000

Hi,

Does there exist in MySQL, or does anyone know of any plans to create, the equivalent of the `json_tree` table-valued function in SQLite?

SELECT fullKey, atom FROM json_tree('
{
"grandparent1": {
"parent1a": {
"child1a1": {
"name": "Alice"
},
"child1a2": {
"name": "Bob"
}
},
"parent1b": {
"child1b1": {
"name": "Carol"
}
}
}
}
');

$|
$.grandparent1|
$.grandparent1.parent1a|
$.grandparent1.parent1a.child1a1|
$.grandparent1.parent1a.child1a1.name|Alice
$.grandparent1.parent1a.child1a2|
$.grandparent1.parent1a.child1a2.name|Bob
$.grandparent1.parent1b|
$.grandparent1.parent1b.child1b1|
$.grandparent1.parent1b.child1b1.name|Carol

Many thanks,
Hamish

JSON_ARRAY_APPEND adding escape slash (12 replies)

Derek McKinnon — Tue, 23 Aug 2022 05:04:17 +0000

I am using MySQL 5.7.

The code will eventually be run in a stored procedure, but I am running in Query editor for debugging.

The following code is inserting \ in as an escape character. The basis of the code is from another forum, but I can't see why it won't work.

-- SET @i = NULL;
SET @i = JSON_ARRAY(JSON_OBJECT('a', '1', 'b', '2'));
SET @j = JSON_OBJECT('c', '3', 'd', '3');
-- SET @j = CAST('{"a": "1"}' AS JSON);
SET @a = IF( @i IS NULL OR JSON_TYPE(@i) != 'ARRAY',
JSON_ARRAY_APPEND(JSON_ARRAY(), '$', @j),
JSON_ARRAY_APPEND(@i, '$', @j)
);
SELECT @j, @a

Results
@j @a
{"c": "3", "d": "3"} [{"a": "1", "b": "2"}, "{\\"c\\": \\"3\\", \\"d\\": \\"3\\"}"]

Note that in my query editor it is only one slash at a time.

I have tried JSON_UNQUOTE and a great number of different arrangements, but I cna't get rid of the escape character. I have read one post suggesting using the REPLACE function, but it seems unlikely to me the developers would have required that.

Power automate json (2 replies)

pavan hnk — Wed, 23 Nov 2022 07:48:16 +0000

I am making flow with power automate which is taking email body and then i am trying to choose specific details from that.

Flow is:

1.Catching email

2.Making email html to text

3.Making newline and splitNewLines

Filtering for array.
But my problem is that my Json is coming with one big strings.

{
"body": [
"Customer: James",
"CustomerId: 1234",
"Address: Street 123",
"Phone: 123455123"
]
}

So they are not coming like this:

{
"body": [
"Customer": "James",
"CustomerId": "1234",
"Address": "Street 123",
"Phone": "123455123"
]
}

Use of SessionStateChanged_CURRENT_SCHEMA? (1 reply)

Geert Vanderkelen — Tue, 07 Mar 2023 05:48:49 +0000

Heya,

I have been looking at the X Plugin source and I don't see the CURRENT_SCHEMA notice being used. It is also not used in the connectors (which are doing a SELECT DATABASE() ..).

Wouldn't it be great if after the authentication (which can include the schema), this notice is provided. Or with every query, so that `USE` triggers it?

Or is the notice SessionStateChanged_CURRENT_SCHEMA not to be used, deprecated?

for json path, for json auto (3 replies)

leland knight — Fri, 15 Oct 2021 00:21:48 +0000

I'm switching a project over from mssql to mysql. In many places there are SELECT statements with 'FOR JSON PATH' appended to the end which with mssql returns formatted JSON, which is then sent to the user via a webapi.

I can see that I can create objects in my C# code, load them up with data, and return the objects which will be converted into JSON.

Does MySQL have something similar or equivalent to 'FOR JSON PATH', in order to avoid having to create the classes for the json conversion process?

Store base64 String using X dev API (no replies)

shashi asanka — Wed, 31 Mar 2021 04:15:06 +0000

Dear All,

we need to user My SQL Document Store as File Storage and is it possible to store base64 String using X dev API. if possible will it be a performance issue when data retrieving ?

Than You !!!

Searching in a nested array (1 reply)

Karl Hörnell — Wed, 24 Feb 2021 12:49:03 +0000

Perhaps this belongs in the newbie forum. I couldn't find any documentation about a particular search case. Suppose I have a collection "scifi_lovers" of documents in this format:

{
"firstname":"John",
"lastname":"Smith",
"books":[
{"title":"Dune", "author":"Frank Herbert"},
{"title":"Fahrenheit 451". "author":"Ray Bradbury"}
]
}

How would I use X DevAPI to find documents where the "books" array contains a specific title?

Benchmarking (1 reply)

Rushikesh Socha — Thu, 05 Nov 2020 17:21:35 +0000

Hi , I have created mysql document store database and imported data from couchbase using json file, I would like to do some kind of benchmarking, Can you please suggest what took i can use to do benchmarking for mysql document store database ?

MySQL: which clients were connected to a server ? (no replies)

Edwin Desouza — Thu, 17 Sep 2020 17:58:22 +0000

MySQL: which clients were connected to a server ?
http://schlueters.de/blog/archives/189-Using-JSON-features-to-restructure-results.html

MySQL: Generated Columns and virtual indexes (no replies)

Edwin Desouza — Mon, 07 Sep 2020 20:15:41 +0000

https://blog.koehntopp.info/2020/09/07/mysql-generated-columns-and-virtual-indexes.html

MySQL: Basic usage of the JSON data type (no replies)

Edwin Desouza — Fri, 04 Sep 2020 20:02:55 +0000

MySQL: Basic usage of the JSON data type
— https://blog.koehntopp.info/2020/09/03/mysql-json-data-type.html

NoSQL Development in MySQL Using Python (no replies)

Edwin Desouza — Mon, 20 Apr 2020 15:25:35 +0000

https://chandanmysqltricks.blogspot.com/2020/04/nosql-development-in-mysql-using-python.html

How could I read out only a part of a database column? (no replies)

DAniel Hofmann — Sun, 19 Apr 2020 10:26:24 +0000

Hey,

i have a entry in a database column as the following:

[{"id":"1","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"Naam:","value":"J.van Roode"},{"id":"2","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"Straatnaam \/ huisnummer:","value":"Boswilg 25"},{"id":"7","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"Postcode:","value":"1853AA"},{"id":"9","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":" Plaatsnaam:","value":"Heiloo"},{"id":"10","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"Telefoonnummer:","value":"0725615318"},{"id":"3","is_email":"true","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"Email","value":"J.vanroode@ziggo.nl"},{"id":"19","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"LET OP !! U ontvangt de bevestiging alleen per E-mail.","value":"true"},{"id":"29","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"Ja, Ik ga accoord met de reserveringsvoorwaarden van enjoybooking, en de nieuwe algemene verordening persoonsgevens ","value":"true"},{"id":"24","is_email":"false","add_to_day_hour_info":"false","add_to_day_hour_body":"false","translation":"Bijzonderheden:","value":"Als er is graag balkon "}]

Now I want to read out only the LAST ID Field with the Bijzonderheden Part in the last text section and I only need to display the value:

"Als er is graag balkon"

The column in the database is called "form" - how could I only read out this part of the entry and not the whole entry?

At the moment I read it out with the following command:

if( !in_array( 'Bijzonderheden', $csvReservationHeader ) ) {
array_push($csvReservationHeader, 'Bijzonderheden');
}
array_push($csvReservation, $reservation['form']);
array_push($excelReservationsData, ''.$reservation['form'].'');

if (!array_key_exists('form', $jsonReservationsData)) {
$jsonReservationsData['form'] = array();
}
@array_push($jsonReservationsData['form'], $reservation['form']);

Hope to get help from you :-)

Best regards
Daniel

MySQL versus MongoDB for storing JSON documents in a doc-store database – which is faster? (no replies)

Edwin Desouza — Fri, 10 Apr 2020 18:33:37 +0000

MySQL versus MongoDB for storing JSON documents in a doc-store database – which is faster?
- https://scriptingmysql.wordpress.com/2020/04/10/mysql-versus-mongodb-for-storing-json-documents-in-a-doc-store-database-which-is-faster/

Trigger crashing due to invalid json path (no replies)

Nuno Donato — Mon, 09 Mar 2020 12:28:02 +0000

Hi folks.
I did a trigger to keep a table updated with a JSON field that stores key-values fetched from a "master" table.
Doing my initial import (insert into t1 from select(... from t2)) it crashes after a while with the message

Invalid JSON path expression. The error is around character position 10.

I'm building my JSON path by concatenating a string and there are no strange characters in the variables names.

Any clues on what I can do to fix/debug this?

here's my trigger

CREATE DEFINER=`root`@`localhost` TRIGGER `datatable_insert` AFTER INSERT ON `datatable` FOR EACH ROW BEGIN
DECLARE mystr char;
IF exists (SELECT data,inst, eq FROM laravel.datastore WHERE `data`=NEW.`data` and inst=NEW.inst and eq=NEW.eq) THEN
SET @mystr = CONCAT("$.",NEW.var);
UPDATE laravel.datastore SET var=JSON_SET(var,@mystr,NEW.val) WHERE `data`=NEW.data and inst=NEW.inst and eq=NEW.eq;
ELSE
INSERT INTO laravel.datastore (data,inst,eq,var) VALUES (NEW.data,NEW.inst,NEW.eq,JSON_OBJECT(NEW.var,NEW.val));
END IF;
END

thanks in advance

Wix: Lessons from Working with NoSQL in a SQL DB & MySQL JSON Data Type Migration (no replies)

Edwin Desouza — Mon, 10 Feb 2020 21:55:53 +0000

Wix: Lessons from Working with NoSQL in a SQL DB & MySQL JSON Data Type Migration
- https://medium.com/wix-engineering/watch-lessons-from-working-with-nosql-in-a-sql-db-mysql-json-data-type-migration-2bb3f674b514

generated _id by MySQL Server (2 replies)

Arik Fastovsky — Sun, 19 Jan 2020 20:06:01 +0000

Hi Forum,

I need help.

When I used Collections as Relational Tables

MySQL server don't generate _id.

Is is true?

Thank!

Could anyone share commands and gcc environment for building MySQL shell? (no replies)

Victor wen — Wed, 01 Jan 2020 00:59:22 +0000

Hi All,

Does anyone know what commands and GCC environment were used for building the official mysqlsh binary file and the RPM?

I was trying to build MySQL shell with the source code.
My final target is to build an RPM which could run on my hosts without upgrade libstdc++ of version libstdc++.so.6.0.0.19. - Just as what the official RPM did.

For the first step I tried to build it with "cmake" and "make mysqlsh", following the INSTALL document. However, the GCC version on my compiling machine is 7.2, the mysqlsh I built has dependencies on libstdc++.so.6.0.0.24 which corresponding lib for GCC 7.2.
I also tried GCC 5.3 and 4.8, but some errors occurred during compiling.

I hope to find a way to build a similar binary file like the official one. Does anyone have some suggestions? Thank you in advance!

Here are some differences of dynamic library references between official binary file and mine:
Official binary file mysqlsh:
Dynamic Section:
NEEDED libstdc++.so.6
...
Version References:
required from libstdc++.so.6:
0x0297f860 0x00 25 GLIBCXX_3.4.10
0x0bafd172 0x00 19 CXXABI_1.3.2
0x02297f89 0x00 14 GLIBCXX_3.4.9
0x0bafd173 0x00 12 CXXABI_1.3.3
0x0297f861 0x00 10 GLIBCXX_3.4.11
0x056bafd3 0x00 09 CXXABI_1.3
0x08922974 0x00 07 GLIBCXX_3.4
...

mysqlsh binary file I built with GCC 7.2.
Dynamic Section:
NEEDED libstdc++.so.6
...
Version References:
required from libstdc++.so.6:
0x0bafd178 0x00 29 CXXABI_1.3.8
0x02297f85 0x00 28 GLIBCXX_3.4.5
0x0297f869 0x00 27 GLIBCXX_3.4.19
0x0297f867 0x00 25 GLIBCXX_3.4.17
0x02297f89 0x00 23 GLIBCXX_3.4.9
0x0bafd175 0x00 20 CXXABI_1.3.5
0x0297f863 0x00 19 GLIBCXX_3.4.13
0x0afd17f1 0x00 18 CXXABI_1.3.11
0x0297f864 0x00 17 GLIBCXX_3.4.14
0x0bafd173 0x00 16 CXXABI_1.3.3
0x0297f872 0x00 15 GLIBCXX_3.4.22
0x0297f865 0x00 14 GLIBCXX_3.4.15
0x0297f871 0x00 13 GLIBCXX_3.4.21
0x0297f868 0x00 12 GLIBCXX_3.4.18
0x0bafd179 0x00 11 CXXABI_1.3.9
0x0297f861 0x00 09 GLIBCXX_3.4.11
0x056bafd3 0x00 07 CXXABI_1.3
0x0297f870 0x00 06 GLIBCXX_3.4.20
0x0bafd172 0x00 05 CXXABI_1.3.2
0x08922974 0x00 02 GLIBCXX_3.4
...

Happy new year!

importJson failed which include mongo date type file (1 reply)

MIN SOO CHO — Thu, 17 Oct 2019 09:21:05 +0000

Hi,
I tried importing https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json file as following :

util.importJson("/home/mysql/primer-dataset.json",{collection:"restaurants",convertBsonTypes:true})ut

but the following error occurred :

Importing from file "/home/mysql/primer-dataset.json" to collection `testdb`.`restaurants` in MySQL Server at mysqlgr1:33060

Processed 0 bytes in 0 documents in 0.0013 sec (0.00 documents/s)
Total successfully imported documents 0 (0.00 documents/s)
Util.importJson: Unexpected data, expected to find a string processing extended JSON for $date at offset 189 (ArgumentError)

When I change convertBsonTypes:true to false, the import was successful but $date data are inserted as is.

How can I convert $date format? and how to find the exact error?

Three New JSON Functions in MySQL 8.0.17 (no replies)

Edwin Desouza — Mon, 22 Jul 2019 15:18:21 +0000

https://elephantdolphin.blogspot.com/2019/07/three-new-json-functions-in-mysql-8017.html

JSON Schema Validation with MySQL 8.0.17 (no replies)

David Stokes — Mon, 22 Jul 2019 13:29:05 +0000

JSON has become the standard document interchange format over the last several years. MySQL 5.7 added a native JSON data type and it has been greatly enhanced with version 8.0. But many in the relational world have complained the the NoSQL approach does not allow you to have rigor on your data. That is to make sure an integer value is really an integer and within specified ranges or string of the proper length. And there was no way to make sure that email addresses are not listed under a combination of E-mail, e-mail, eMail, and eMAIL. JSON is great for many things but traditional, normalized data was better for making certain that your data matched what was specified.

If only there was a way to enforce come rigor on JSON data! Or a way to annotate (pronounced 'document') your JSON data. Well there is. MySQL 8.0.17 has added the ability to validate JSON documents against a schema following the guidelines of the JSON-Schema.org's fourth draft standard. You can find both the manual page 12.17.7 JSON Schema Validation Functions and the JSON Schema information online.

https://elephantdolphin.blogspot.com/2019/07/json-schema-validation-with-mysql-8017.html

TableInsert options for INSERT IGNORE and REPLACE INTO? (no replies)

Brady Brown — Mon, 08 Jul 2019 19:05:16 +0000

Hi,

Any plans to support these variations on INSERTs in mysqlx? Or are they supported elsewhere in the API?

Thanks,

-b

MySQL DocStore: Import from MongoDB (no replies)

Edwin Desouza — Mon, 29 Apr 2019 04:30:25 +0000

Import from MongoDB:
- https://lefred.be/content/top-10-reasons-for-nosql-with-mysql/
- https://mysqlserverteam.com/importing-data-from-mongodb-to-mysql-bson-data-types/
- https://mysqlserverteam.com/importing-data-from-mongodb-to-mysql-using-python/
- https://mysqlserverteam.com/importing-data-from-mongodb-to-mysql-using-javascript/

MySQL 8.0: Window Functions for JSON (no replies)

Edwin Desouza — Thu, 21 Feb 2019 17:08:24 +0000

MySQL 8.0: Window Functions for JSON
https://mysqlserverteam.com/json-specific-window-functions-in-mysql-8-0/

Importing Data from MongoDB to MySQL: BSON Data Types (no replies)

Edwin Desouza — Fri, 08 Feb 2019 22:15:39 +0000

https://mysqlserverteam.com/importing-data-from-mongodb-to-mysql-bson-data-types/

MySQL Connector/NodeJS: working with result sets (no replies)

Edwin Desouza — Wed, 19 Dec 2018 13:28:47 +0000

MySQL Connector/NodeJS: working with result sets
https://insidemysql.com/working-with-result-sets-in-connector-node-js/