UNNEST JSON数组喜欢SQL行

Question

我可以有这样的JSON:

[
{ 
"number": 123, 
"items" : [
{"product": "P1","cost":10.5},
{"product": "P2","cost":5.25}
],
"tags":["a","b","c"],
"customer": {
"name": "Roberto",
"shortName": "Beto"
},
"actions": [{
"moment": "2021-01-01 11:22:22.222",
"description": "action 1"
},{
"moment": "2021-01-23 11:22:22.222",
"description": "action 2"
}]
}
]

我需要获得输出，如使用joins查询的SQL结果。注意，输出必须基于复杂的关联进行协调(在上面的例子中，是属性:项和操作):

[
{ "number": 123, "items_product" : "P1", "items_cost": 10.5, "tags": null, "actions_moment": null, "actions_description": null}
{ "number": 123, "items_product" : "P2", "items_cost": 5.25, "tags": null, "actions_moment": null, "actions_description": null}
{ "number": 123, "items_product" : null, "items_cost": null, "tags": "a", "actions_moment": null, "actions_description": null}
{ "number": 123, "items_product" : null, "items_cost": null, "tags": "b", "actions_moment": null, "actions_description": null}
{ "number": 123, "items_product" : null, "items_cost": null, "tags": "c", "actions_moment": null, "actions_description": null}
{ "number": 123, "items_product" : null, "items_cost": null, "tags": null, "actions_moment": "2021-01-01 11:22:22.222", "actions_description": "Expedicao"}
{ "number": 123, "items_product" : null, "items_cost": null, "tags": null, "actions_moment": "2021-01-23 11:22:22.222", "actions_description": "Bla bla bla"}
]

我需要使用Java和Google Gson动态生成输出。

Answer 1

实现此目的的简单方法如下2步:
步骤1:为输出创建POJO类

class Output {
private int number;
@SerializedName("items_product")
private String itemsProduct;
@SerializedName("items_cost")
private float itemsCost ;
private String tags;
@SerializedName("actions_moment")
private String actionsMoment;
@SerializedName("actions_description")
private String actionsDescription;
public Output(int number) {
this.number = number;
}
//general getters and setters
}

步骤2:解析给定的JSON字符串并将其存储为预定义的Output类。

Gson gson = new GsonBuilder().serializeNulls().setPrettyPrinting().create();
JsonElement root = gson.fromJson(inputJsonStr, JsonElement.class);
List<Output> outputList = new ArrayList<>();
root.getAsJsonArray().forEach(e -> {
JsonObject element = e.getAsJsonObject();
int number = element.get("number").getAsInt()
element.get("items").getAsJsonArray().forEach(i -> {
Output output = new Output(number);
output.setItemsProduct(i.getAsJsonObject().get("product").getAsString());
output.setItemsCost(i.getAsJsonObject().get("cost").getAsFloat());
outputList.add(output);
})
element.get("tags").getAsJsonArray().forEach(t -> {
Output output = new Output(number);
output.setTags(t.getAsString());
outputList.add(output);
})
element.get("actions").getAsJsonArray().forEach(a -> {
Output output = new Output(number);
output.setActionsMoment(a.getAsJsonObject().get("moment").getAsString());
output.setActionsDescription(a.getAsJsonObject().get("description").getAsString());
outputList.add(output);
});
})
System.out.println(gson.toJson(outputList));