🧾 Parse JSON Grid Data and Insert Child Rows in Joget

1. Overview

Joget grid fields often store rows as JSON. This article shows a BeanShell pattern for reading JSON grid data from one form record and inserting each row into a child table for reporting, searching, or downstream processing.

2. How It Works

• Load the parent request record using the current form ID.
• Read the JSON grid field value.
• Parse the value as a JSONArray.
• Validate required row values.
• Insert each valid row into a child form table with a generated UUID.
• Delete old child rows first so the child table mirrors the latest grid data.

3. Where to Use in Joget

• Form Builder: post-processing tool after a form is submitted.
• Workflow Builder: Tool activity after request submission.
• Datalist/Reports: when grid rows need to become searchable child records.

4. Full Code

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.model.Element;
import org.joget.commons.util.LogUtil;
import org.joget.commons.util.UuidGenerator;

import java.io.*;  
import java.io.IOException;  
import java.util.HashMap;  
import java.util.Map;

import org.json.JSONException;
import org.json.JSONObject;
import org.json.JSONArray;

Connection con = null;
String record_id ="#form.parent_request.id#";
String dateCreated = "#date.YYYY-MM-dd HH:mm:ss#";
String dateModified = dateCreated;
String createdBy = "#currentUser.username#";
String createdByName = "#currentUser.fullName#";
String modifiedBy = createdBy;
String modifiedByName = createdByName;

try {
    DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
    con = ds.getConnection();
    if (!con.isClosed()) {
        LogUtil.info("connection established", "record id " + record_id);
        PreparedStatement stmt_get = con.prepareStatement("select * from app_fd_parent_request where id=?");
        stmt_get.setObject(1, record_id);
        ResultSet rs_get = stmt_get.executeQuery();
        while (rs_get.next()) {

            // delete existing records
            stmt_delete = con.prepareStatement("delete from app_fd_child_rows where c_parent_id=?");
            stmt_delete.setObject(1, record_id);
            stmt_delete.executeQuery();

            if (rs_get.getString("c_grid_data") != null && !rs_get.getString("c_grid_data").isEmpty()) {
                String grid_data = rs_get.getString("c_grid_data");
                JSONArray grid_data_array = new JSONArray(grid_data);
                for (int i = 0; i < grid_data_array.length(); i++) {
                    JSONObject grid_data_object = grid_data_array.get(i);

                    String request_no = grid_data_object.optString("request_no", null);
                    String year_value = grid_data_object.optString("year_value", null);
                    String document_no = grid_data_object.optString("document_no", null);
                    String item_no = grid_data_object.optString("item_no", null);
                    String remarks = grid_data_object.optString("remarks", null);

                    // Check if any field is empty or null
                    if (request_no != null && year_value != null && document_no != null && item_no != null
                        && !request_no.isEmpty() && !year_value.isEmpty() && !document_no.isEmpty() && !item_no.isEmpty()) {
                        // Insert data into the database only if all fields are non-empty
                        PreparedStatement stmt_insert = con.prepareStatement("insert into app_fd_child_rows(id,dateCreated,dateModified,createdBy,createdByName,modifiedBy,modifiedByName,c_parent_id,c_request_no,c_year_value,c_item_no,c_document_no,c_remarks) values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                        String uuid = UuidGenerator.getInstance().getUuid();
                        stmt_insert.setObject(1, uuid);
                        stmt_insert.setObject(2, dateCreated);
                        stmt_insert.setObject(3, dateModified);
                        stmt_insert.setObject(4, createdBy);
                        stmt_insert.setObject(5, createdByName);
                        stmt_insert.setObject(6, modifiedBy);
                        stmt_insert.setObject(7, modifiedByName);
                        stmt_insert.setObject(8, record_id);
                        stmt_insert.setObject(9, request_no);
                        stmt_insert.setObject(10, year_value);
                        stmt_insert.setObject(11, item_no);
                        stmt_insert.setObject(12, document_no);
                        stmt_insert.setObject(13, remarks);
                        stmt_insert.executeQuery();
                    }
                }
            }
        }
    }
} catch (Exception e) {
    LogUtil.info("Catch Block", "Exception Found " + e.toString());
} finally {
    try {
        if (con != null)
            con.close();
    } catch (Exception e) {
        LogUtil.info("Final Catch Block", "Exception Found");
    }
}

5. Example Use Cases

• Converting grid request items into child records.
• Preparing JSON grid data for reports.
• Creating searchable transaction rows from a parent form.
• Syncing editable grid values into normalized tables.

6. Customization Tips

• Replace parent and child table names with your Joget form table names.
• Change JSON keys to match your grid column IDs.
• Use executeUpdate() for insert and delete operations in stricter JDBC environments.
• Add transaction handling if the child rows must be all-or-nothing.

7. Key Benefits

• Makes grid data easier to report and query.
• Keeps child records synchronized with the parent form.
• Avoids manual parsing in dashboards or datalists.
• Uses standard Joget datasource access.

8. Security Note

Validate JSON content before inserting it into database tables. Avoid logging full business data if the grid contains sensitive financial or personal information.

9. Final Thoughts

This approach is practical when Joget grid JSON needs to become structured records. Keep the mapping clear, validate required fields, and test with empty grids and partially filled rows.