Description:
1. The content of one of JSON document in world_x sample database as below:
{
"GNP" : 256254,
"HeadOfState" : "Tsai Ying-Wen",
"IndepYear" : 1945,
"Name" : "Taiwan",
"_id" : "TWN",
"demographics" : {
"LifeExpectancy" : 76.4000015258789,
"Population" : 22256000
},
"geography" : {
"Continent" : "Asia",
"Region" : "Eastern Asia",
"SurfaceArea" : 36188
},
"government" : {
"GovernmentForm" : "Republic",
"HeadOfState" : "Chen Sui-Ban"
}
}
2. Create Java program with the following code to retrieve HeadOfState from the above JSON document in world_x database with the following code:
SessionFactory sFact = new SessionFactory();
Session mySess = sFact.getSession("mysqlx://<hostname>:33060/world_x?user=root&password=<password>");
Schema myDb = mySess.getSchema("world_x");
Collection myColl = myDb.getCollection("countryInfo");
DocResult myDocs = myColl.find("Name like :name").bind("name","Taiwan").execute();
while (myDocs.hasNext()) {
DbDoc myDoc = myDocs.fetchOne();
JsonValue goVal = mDoc.get("government");
System.out.println(((DbDoc)goVal).get("HeadOfState"));
}
3. As the above code shows, to retrieve values that are located at 2 or more layer deeper in the JSON document (such as HeadOfState), I need to create intermediate object (goval), there is no way to access them directly with one DbDoc.get() method such as mDoc.get(".government.HeadOfState")
4. the path of ".government.HeadOfState" is borrowed from Collection.modify() method (for example, myColl.modify("Name = :name").set(".government.HeadOfState", "Tsai Ying-Wen").bind("name","Taiwan);)
How to repeat:
1. download and import world_x database to your MySQL 8.0 instance
2. set classpath environment variable to mysql-connector-java-XXX-8.0.12.jar
3. create Java program and copy/paste the codes in 2nd point of description
4. run the Java program and try to change it to access HeadOfState with a single DbDoc.get() statement, without create intermediate object
Suggested fix:
it would be more convenient to retrieve values out of the JSON document if there is a way to access value with key value similar to JSON path (especially when the structure is complex). For example:
DbDoc.get("$layer1FieldA.layer2FieldB.layer3Array[1]"); to return the value that located at 2nd array of 3 layer from the root
Description: 1. The content of one of JSON document in world_x sample database as below: { "GNP" : 256254, "HeadOfState" : "Tsai Ying-Wen", "IndepYear" : 1945, "Name" : "Taiwan", "_id" : "TWN", "demographics" : { "LifeExpectancy" : 76.4000015258789, "Population" : 22256000 }, "geography" : { "Continent" : "Asia", "Region" : "Eastern Asia", "SurfaceArea" : 36188 }, "government" : { "GovernmentForm" : "Republic", "HeadOfState" : "Chen Sui-Ban" } } 2. Create Java program with the following code to retrieve HeadOfState from the above JSON document in world_x database with the following code: SessionFactory sFact = new SessionFactory(); Session mySess = sFact.getSession("mysqlx://<hostname>:33060/world_x?user=root&password=<password>"); Schema myDb = mySess.getSchema("world_x"); Collection myColl = myDb.getCollection("countryInfo"); DocResult myDocs = myColl.find("Name like :name").bind("name","Taiwan").execute(); while (myDocs.hasNext()) { DbDoc myDoc = myDocs.fetchOne(); JsonValue goVal = mDoc.get("government"); System.out.println(((DbDoc)goVal).get("HeadOfState")); } 3. As the above code shows, to retrieve values that are located at 2 or more layer deeper in the JSON document (such as HeadOfState), I need to create intermediate object (goval), there is no way to access them directly with one DbDoc.get() method such as mDoc.get(".government.HeadOfState") 4. the path of ".government.HeadOfState" is borrowed from Collection.modify() method (for example, myColl.modify("Name = :name").set(".government.HeadOfState", "Tsai Ying-Wen").bind("name","Taiwan);) How to repeat: 1. download and import world_x database to your MySQL 8.0 instance 2. set classpath environment variable to mysql-connector-java-XXX-8.0.12.jar 3. create Java program and copy/paste the codes in 2nd point of description 4. run the Java program and try to change it to access HeadOfState with a single DbDoc.get() statement, without create intermediate object Suggested fix: it would be more convenient to retrieve values out of the JSON document if there is a way to access value with key value similar to JSON path (especially when the structure is complex). For example: DbDoc.get("$layer1FieldA.layer2FieldB.layer3Array[1]"); to return the value that located at 2nd array of 3 layer from the root