如何将Firebase动态JSON数据存入内存数据库或转为POJO？

Hey there, let's work through your problem—dealing with dynamic JSON from Firebase, figuring out the best way to turn it into tables and charts without getting stuck on POJOs or relational database headaches. I’ve been in similar spots with unpredictable JSON structures, so here’s what I’d recommend:

1. Skip POJOs & Directly Manipulate Dynamic JSON (Fastest for Prototyping) #

Since your JSON has dynamic fields (like varying ReasonX keys and page-specific data), forcing it into rigid POJOs is going to be a nightmare—you’ll end up with endless annotations or unused fields. Instead, use a flexible JSON library to parse and traverse the data on the fly:

Example with Jackson (Java) #

ObjectMapper mapper = new ObjectMapper();
JsonNode rootNode = mapper.readTree(firebaseJsonString);

// Iterate over each Work Order entry
for (Map.Entry<String, JsonNode> entry : rootNode.fields()) {
    // Clean up the work order ID from the key
    String workOrderId = entry.getKey().replace("Work Order: Optional(", "").replace(")", "");
    JsonNode workOrderData = entry.getValue();

    // Extract fixed fields easily
    String managerName = workOrderData.get("Manager Name").asText();
    String technicianName = workOrderData.get("Technician Name").asText();
    String when = workOrderData.get("When").asText();

    // Traverse dynamic page data
    JsonNode secondPage = workOrderData.get("secondPage");
    if (secondPage != null) {
        // Handle dynamic keys like "Reason1", "Reason2"
        for (Map.Entry<String, JsonNode> pageField : secondPage.fields()) {
            String fieldName = pageField.getKey();
            String fieldValue = pageField.getValue().asText();
            // Use this data to build table rows or chart datasets
        }
    }
}

Why this works for your use case: #

• No need to define rigid structures—you can adapt to new dynamic fields automatically.
• Perfect for generating tables: you can flatten each work order’s data into rows, even including dynamic page fields as extra columns.
• For charts, you can aggregate values (e.g., count how many times "YES" appears for "Appropriate PPE was used?") as you traverse the JSON.

2. Ditch H2 for a Document-Oriented In-Memory Database (Better for Queries/Stats) #

Relational databases like H2 rely on fixed schemas, which clash with your dynamic JSON. Instead, use a document-based in-memory database that natively supports flexible structures:

Options to try: #

• MongoDB Memory Server: Runs a MongoDB instance in memory, lets you store each work order as a document (no schema required). You can use MongoDB’s aggregation framework to quickly compute stats for charts (e.g., group by "Manager Name" and count completed jobs).
• Apache CouchDB (In-Memory Mode): Similar to MongoDB, stores JSON documents natively and supports map-reduce queries for aggregation.

Example workflow with MongoDB Memory Server: #

1. Insert each work order as a document (directly from your Firebase JSON).
2. Run an aggregation query to get stats for your chart:

   db.workOrders.aggregate([
       { $match: { "thirdPage.The work performed was of Stanford,Facilities Operations and Trade Specific quality?": "YES" } },
       { $group: { _id: "$Manager Name", highQualityJobs: { $sum: 1 } } }
   ])
   

3. Use the aggregated results to build your chart (e.g., a bar graph of managers with the most high-quality jobs).

3. Compromise: Semi-Structured Storage with Relational Databases (If You Must Use H2) #

If you’re set on using H2, don’t try to map every dynamic field to a column. Instead, split your data into fixed and dynamic parts:

Table Structure Idea: #

How to use this: #

• Use H2’s built-in JSON functions to query dynamic fields when needed (e.g., JSON_VALUE(pages_data, '$.secondPage."Scope of work (SOW) was accurately achieved?"')).
• This keeps your table schema stable while still letting you access dynamic data for tables and charts.

Final Recommendations Based on Your Goals #

• Quick table generation: Go with direct JSON manipulation (Jackson/Gson)—it’s fast and requires no extra setup.
• Complex chart stats: Use a document-oriented in-memory database—aggregation queries will save you hours of manual counting.
• Existing relational stack: Stick with semi-structured storage in H2—avoid forcing dynamic fields into fixed columns.

内容的提问来源于stack exchange，提问作者Hussein Nagri