UNNEST JSON array to like SQL rows

Question

I can have JSON like this:

[
  { 
    "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"
    }]
  }
]

I need to get outputs like a SQL result of a query using joins. Note that the output must be coordinated based on the complex associations (in the case above, the properties: items and actions):

[
  { "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"}
]

I need to generate output dynamically using Java and Google Gson.

Duplicated from: https://stackoverflow.com/questions/49876937/transform-and-flatten-json-using-gson — Daniel Campos Olivares, Oct 19 '21 at 14:14
Does this answer your question? [Transform and flatten JSON using GSON](https://stackoverflow.com/questions/49876937/transform-and-flatten-json-using-gson) — Pirate, Oct 19 '21 at 14:15
This is not the same question, the expected result is different. — Beto Neto, Oct 19 '21 at 14:33

LHCHIN · Answer 1 · 2021-10-20T03:04:35.920

A straightforward way to achieve this is shown as following 2 steps:
Step 1: Create a POJO class for output.

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
}

Step 2: Parse given JSON string and store it as predefined Output class.

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));

A straightforward way is not an option for me (sorry, my question was not so detailed, I have improved it now) — Beto Neto, Oct 21 '21 at 14:55
Hi, your explanation is still not clear enough. I cannot see the relationship between `items` and `actions` as what you said. — LHCHIN, Oct 22 '21 at 00:38

UNNEST JSON array to like SQL rows

1 Answers1