Bug #92679 Can't directly accss values that at 2 or more layers of the JSON document
Submitted: 5 Oct 2018 4:30 Modified: 5 Oct 2018 9:25
Reporter: Ivan Tu Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:8.0.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: json
Triage: Needs Triage: D5 (Feature request)

[5 Oct 2018 4:30] Ivan Tu
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
[5 Oct 2018 9:25] Umesh Shastry
Hello Ivan,

Thank you for the report and test case.
After discussing internally with Alex, handling this as a feature request.

regards,
Umesh