Spring query convert to a nested JSON structure

Question

I'm new to spring and Java and trying to figure out how to go about formatting the json response into the desired structure.

I have a spring query that's returning 2 columns from a table like below which are really the key and values I need for the json structure:

Names	Values
Car	Toyota
Bike	Schwinn
Scooter	Razor
A0	11
A1	12
A2	13
B0	2000
B1	4000
B2	22000

The current json output from the controller is this:

[{
        "names": "Car",
        "values": "Toyota"
    },
    {
        "names": "Bike",
        "values": "Schwinn"
    },
    {
        "names": "Scooter",
        "values": "Razor"
    },
    {
        "names": "A0",
        "values": "11"
    },
    {
        "names": "A1",
        "values": "12"
    },
    {
        "names": "A2",
        "values": "13"
    },
    {
        "names": "B0",
        "values": "2000"
    },
    {
        "names": "B1",
        "values": "4000"
    },
    {
        "names": "B2",
        "values": "22000"
    }
]

And the desired json format is this where the table column names are removed and instead json structure is created using the names column for the keys:

{
    "Car": "Toyota",
    "Bike": "Schwinn",
    "Scooter": "Razor",
    "Data": [{
        "A0": "11",
        "B0": "2000"
    }, {
        "A1": "12",
        "B1": "4000"
    }, {
        "A2": "13",
        "B2": "22000"
    }]
}

Repository

   @Query (value = "Select names, values ... :id")
    List findData(@Param("id") Long id) ;

      interface Data {
        String getnames();
        String getvalues();
    }

Service

    public List getData(Long id) {return repo.findData(id);}

Controller

    @GetMapping("/getdata/{id}")
    public ResponseEntity> getData(@PathVariable Long id) {
            List c = service.getData(id);
            return new ResponseEntity<>(c, HttpStatus.OK);

    }

It seems that I need to process the result set and need to loop through them to create the desired structure but not sure how to proceed with that, or perhaps there is an easier way to get to the desired structure. Any guidance would be appreciated.

Krusty the Clown · Answer

So return a ResponseEntity> instead of a List to simulate a Json object.

List c = service.getData(id);

Map map = new HashMap<>();
map.put("Key", "Value");
map.put("Car", c.get(0).getvalues());
map.put("Entire List", c);

return new ResponseEntity<>(c, HttpStatus.OK);

Obviously you'll have to write your own logic but it should be pretty straight forward. Or, even better, consider making a class for the object returned if you're going to be using it a lot, and just return ResponseEntity< YourCustomObject >

Black_Dragon · Answer

This looks a bit complicated, I think you should set the primary key association for values like A0 B0


import com.black_dragon.utils.JacksonUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static java.util.stream.Collectors.groupingBy;

/**
 * @author black_dragon
 * @version V1.0
 * @Package com.black_dragon.swing
 * @date 2022/9/6 10:35
 * @Copyright
 */
public class ConvertToMap {
    String names;
    String values;

    public String getNames() {
        return names;
    }

    public void setNames(String names) {
        this.names = names;
    }


    public String getValues() {
        return values;
    }

    public void setValues(String values) {
        this.values = values;
    }

    private static String DIGIT_REGEX = "[^0-9]";

    private static String LETTER_DIGIT_REGEX = "[a-zA-Z]+";

    public static Integer getDigit(String str){
        Pattern pattern = Pattern.compile(DIGIT_REGEX);
        if(!isLetterDigit(str)){
            String[] keySet = pattern.split(str);
            if(keySet.length > 0){
                return Integer.valueOf(keySet[1]);
            }
        }
        return -1;
    }

    public static boolean isLetterDigit(String str){
        return str.matches(LETTER_DIGIT_REGEX);
    }

    private static String fetchGroupKey(ConvertToMap convertToMap){
        return String.valueOf(getDigit(convertToMap.names));
    }

    public static void main(String[] args) {
        String jsonString = "[{
" +
                "        \"names\": \"Car\",
" +
                "        \"values\": \"Toyota\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"Bike\",
" +
                "        \"values\": \"Schwinn\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"Scooter\",
" +
                "        \"values\": \"Razor\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"A0\",
" +
                "        \"values\": \"11\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"A1\",
" +
                "        \"values\": \"12\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"A2\",
" +
                "        \"values\": \"13\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"B0\",
" +
                "        \"values\": \"2000\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"B1\",
" +
                "        \"values\": \"4000\"
" +
                "    },
" +
                "    {
" +
                "        \"names\": \"B2\",
" +
                "        \"values\": \"22000\"
" +
                "    }
" +
                "]";

        List convertToMaps = JacksonUtils.toJavaList(jsonString, ConvertToMap.class);

        // Extract a string that does not contain numbers and convert it to a map
        Map result = convertToMaps.stream()
                .filter(x -> isLetterDigit(x.names))
                .collect(Collectors.toMap(ConvertToMap::getNames, ConvertToMap::getValues));
        List> mapList = new ArrayList<>();
        // Group by string numbers containing numbers
        Map> stringListMap = convertToMaps.stream().collect(groupingBy(convertToMap -> fetchGroupKey(convertToMap)));
        for (String key : stringListMap.keySet()) {
            if(Integer.valueOf(key) >= 0){
                mapList.add(stringListMap.get(key)
                        .stream()
                        .collect(Collectors.toMap(ConvertToMap::getNames, ConvertToMap::getValues)));
            }
        }
        result.put("Data", mapList);
        System.out.println(JacksonUtils.toJSONString(result));
    }
}

Raymond Choi · Answer

Assume that your data key name pattern is one non-digit followed by digits.

https://github.com/octomix/josson

Deserialization

Josson josson = Josson.fromJsonString(
    "[" +
    "    {" +
    "        \"names\": \"Car\"," +
    "        \"values\": \"Toyota\"" +
    "    }," +
    "    {" +
    "        \"names\": \"Bike\"," +
    "        \"values\": \"Schwinn\"" +
    "    }," +
    "    {" +
    "        \"names\": \"Scooter\"," +
    "        \"values\": \"Razor\"" +
    "    }," +
    "    {" +
    "        \"names\": \"A0\"," +
    "        \"values\": \"11\"" +
    "    }," +
    "    {" +
    "        \"names\": \"A1\"," +
    "        \"values\": \"12\"" +
    "    }," +
    "    {" +
    "        \"names\": \"A2\"," +
    "        \"values\": \"13\"" +
    "    }," +
    "    {" +
    "        \"names\": \"B0\"," +
    "        \"values\": \"2000\"" +
    "    }," +
    "    {" +
    "        \"names\": \"B1\"," +
    "        \"values\": \"4000\"" +
    "    }," +
    "    {" +
    "        \"names\": \"B2\"," +
    "        \"values\": \"22000\"" +
    "    }" +
    "]");

Transformation

JsonNode node = josson.getNode(
    "@collect([names !=~ '\D\d+']*" +
    "         .map(names::values)" +
    "        ,[names =~ '\D\d+']*" +
    "         .group(names.substr(1), map(names::values))@" +
    "         .elements" +
    "         .mergeObjects()" +
    "         .@toObject('Data')" +
    ")" +
    ".flatten(1)" +
    ".mergeObjects()");
System.out.println(node.toPrettyString());

Output

{
  "Car" : "Toyota",
  "Bike" : "Schwinn",
  "Scooter" : "Razor",
  "Data" : [ {
    "A0" : "11",
    "B0" : "2000"
  }, {
    "A1" : "12",
    "B1" : "4000"
  }, {
    "A2" : "13",
    "B2" : "22000"
  } ]
}

Spring query convert to a nested JSON structure

3 Answers