Transform String into nested Json (Java)

I would like the guidence from you all, i'm confused in how to go on in a situation at Java + Spring Boot.

I receive from the database 2 columns of strings, the first column is a path separeted by a slash(/) like "crumbs[0]/link/path" and the second column have the value assigned to the first column, and what i'm trying to do is to create a nested JSON with this.

For example, i'm receiving from the database the following response in two columns like a said before:

COLUMN 1(PATH), COLUMN 2(VALUE)
"crumbs[0]/link/path", "/pokemon/type/pokemon?lang=en"
"crumbs[0]/link/wid", "tablePokemon",
"crumbs[0]/name", "Pokemon"
"data/records[1]/id", "Pikachu"
"data/records[1]/link/path": "/pokemon/type/eletric/pikachu",
"data/records[1]/link/wid": "tableEletric",
"data/records[1]/available": "true",
"data/records[2]/id", "Bulbasaur"
"data/records[2]/link/path": "/pokemon/type/grass/bulbasaur",
"data/records[2]/link/wid": "tableGrass",
"data/records[2]/available": "true",

With this response from database, i'm trying to get this result in Java:

"crumbs": [
           {
            "link": {
                 "path": "/pokemon/type/pokemon?lang=en",
                 "wid": "tablePokemon"
            },
            "name": "Pokemon"
           }
],
"data": {
         "records": [
                     {
                      "id": "Pikachu",
                      "link": {
                            "path": "/pokemon/type/eletric/pikachu",
                            "wid": "tableEletric"
                      },
                      "available": "true",
                      },
                      {
                       "id": "Bulbasaur",
                       "link": {
                             "path": "/pokemon/type/grass/bulbasaur",
                             "wid": "tableGrass"
                       },
                       "available": "true",
                       }                    
                    ]
}

You guys would have any suggestions for me to achieve this objective?

Thank you all for your time, appreciate any help.

You can easily construct a JSON with com.fasterxml.jackson.core.JsonPointer!

Some details on parsing a JsonPath and constructing a Json from it is mentioned here https://stackoverflow.com/questions/51971642/how-to-add-new-node-to-json-using-jsonpath

You could made use of the code from the above reference to build your code.
Add the com.fasterxml.jackson dependencies to your pom.xml

		<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>org.glassfish</groupId>
<artifactId>javax.json</artifactId>
<version>1.1.2</version>
</dependency>

private static final ObjectMapper mapper = new ObjectMapper();

public static void main(String[] args) {
		
/** I'm creating a below map to hold the values you have mentioned in the above case.
 While using JsonPointer I found two issues with the key mentioned here
 1. Key doesnt start with a  / . I'm appending a / with the key while inserting to map 
 2. The arrays in data eg:crumbs[0]/link/path should be represented like crumbs/0/link/path ( I haven't handled this in the code, but it doesn't make much difference in the output)
**/

		Map<String, String> databaseKeyValues = new HashMap<String, String>();
		databaseKeyValues.put("crumbs[0]/link/path", "/pokemon/type/pokemon?lang=en");
		databaseKeyValues.put("crumbs[0]/link/wid", "tablePokemon");
		databaseKeyValues.put("crumbs[0]/name", "Pokemon");
		databaseKeyValues.put("data/records[1]/id", "Pikachu");
		databaseKeyValues.put("data/records[1]/link/path", "/pokemon/type/eletric/pikachu");
		databaseKeyValues.put("data/records[1]/link/wid", "tableEletric");
		databaseKeyValues.put("data/records[1]/available", "true");
		databaseKeyValues.put("data/records[2]/id", "Bulbasaur");
		databaseKeyValues.put("data/records[2]/link/path", "/pokemon/type/grass/bulbasaur");
		databaseKeyValues.put("data/records[2]/link/wid", "tableGrass");
		databaseKeyValues.put("data/records[2]/available", "true");

		ObjectNode rootNode = mapper.createObjectNode();
		
		for(java.util.Map.Entry<String, String> e:databaseKeyValues.entrySet()) {
		setJsonPointerValue(rootNode, JsonPointer.compile("/"+e.getKey()), //Adding slash to identify it as the root element, since our source data didn't have proper key!
				new TextNode(e.getValue()));
		}

		try {
			System.out.println(mapper.writerWithDefaultPrettyPrinter().writeValueAsString(rootNode));
		} catch (JsonProcessingException e1) {
			e1.printStackTrace();
		}
	}
	
	public static void setJsonPointerValue(ObjectNode node, JsonPointer pointer, JsonNode value) {
	    JsonPointer parentPointer = pointer.head();
	    JsonNode parentNode = node.at(parentPointer);
	    String fieldName = pointer.last().toString().substring(1);

	    if (parentNode.isMissingNode() || parentNode.isNull()) {
	        parentNode = mapper.createObjectNode();
	        setJsonPointerValue(node,parentPointer, parentNode); // recursively reconstruct hierarchy
	    }

	    if (parentNode.isArray()) {
	        ArrayNode arrayNode = (ArrayNode) parentNode;
	        int index = Integer.valueOf(fieldName);
	        // expand array in case index is greater than array size (like JavaScript does)
	        for (int i = arrayNode.size(); i <= index; i++) {
	            arrayNode.addNull();
	        }
	        arrayNode.set(index, value);
	    } else if (parentNode.isObject()) {
	        ((ObjectNode) parentNode).set(fieldName, value);
	    } else {
	        throw new IllegalArgumentException("`" + fieldName + "` can't be set for parent node `"
	                + parentPointer + "` because parent is not a container but " + parentNode.getNodeType().name());
	    }
	}

Output:

{
  "data" : {
    "records[1]" : {
      "id" : "Pikachu",
      "link" : {
        "wid" : "tableEletric",
        "path" : "/pokemon/type/eletric/pikachu"
      },
      "available" : "true"
    },
    "records[2]" : {
      "available" : "true",
      "link" : {
        "wid" : "tableGrass",
        "path" : "/pokemon/type/grass/bulbasaur"
      },
      "id" : "Bulbasaur"
    }
  },
  "crumbs[0]" : {
    "link" : {
      "path" : "/pokemon/type/pokemon?lang=en",
      "wid" : "tablePokemon"
    },
    "name" : "Pokemon"
  }
}

The json arrays records[1], records[2], crumbs[0] would be sorted out once we handle the JsonPath from crumbs[0]/link/path to crumbs/0/link/path. Just some string operations would help (iterate through the values and replace '[0]' with '/0/', you could write a regex to pattern match and replace!).

You will need to parse the paths, then build some kind of tree object in memory, and finally convert the tree that you built into JSON.

Here are some tips:

• Start by defining an empty root element. You can use a Map. Keys will be strings, values will be either strings, lists or maps.
• For each path, split it by "/".
• For each path element except the last, check if it is a list or a subtree. You can distinguish this by the presence of [n] at the end of the string.
• Create all intermediate nodes for the path except for the last one. Starting from root (which is a Map), add either a List or a Map for each element if it doesn't exist yet under that name. If it already exists, check that it is what you need it to be. In case of List, append the element. In case of Map, create a sub-entry.
• For the last path element, add it as a String.
• Repeat this for all paths to fill your tree.
• When you are finished, use a combination of recursion and StringBuiders to construct the output string. Alternatively, if you only used strings, maps and lists, you can also use a library such as Jackson to produce JSON.

Note that you don't have information about the length of the lists, so this conversion will not be reversible.