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.

Does this answer your question? Transform and flatten JSON using GSON — Suraj
– Suraj, Commented Oct 19, 2021 at 14:15
This is not the same question, the expected result is different. — Beto Neto
– Beto Neto, Commented Oct 19, 2021 at 14:33

LHCHIN · Accepted Answer · 2021-10-20 03:04:35Z

0

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

edited Oct 20, 2021 at 3:04

answered Oct 20, 2021 at 1:27

LHCHIN

4,0782 gold badges19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Beto Neto Over a year ago

A straightforward way is not an option for me (sorry, my question was not so detailed, I have improved it now)

LHCHIN Over a year ago

Hi, your explanation is still not clear enough. I cannot see the relationship between items and actions as what you said.

Collectives™ on Stack Overflow

UNNEST JSON array to like SQL rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related