If i have this JSON to store in questdb, how would you recommend i handle the nested portions of it?
As far as searching goes, i do need to search within the json. Should i just flatten the json?
the JSON provided is what we post to a Kafka topic. we want to move this to questdb for establishing a historical state of records.
{
"nested_1": {
"nested_2": {
"field_1": "2025-04-01T08:14:52.810Z",
"field_2": "2025-04-01T08:14:53.173Z",
"field_3": "2025-04-01T08:15:26.241661Z",
"field_4": "2025-04-01T08:15:47.002053Z"
}
},
"nested_3": {
"field_5": "[REDACTED]",
"field_6": "[REDACTED]",
"nested_4": {
"field_7": "[REDACTED]"
},
"field_8": "[REDACTED]",
"field_9": "AWS"
},
"nested_5": {},
"nested_6": [
{
"field_10": "[REDACTED]",
"field_11": "[REDACTED]"
}
],
"field_12": "[REDACTED]",
"field_13": "[REDACTED]",
"nested_7": {
"field_14": null,
"field_15": null,
"field_16": null
},
"modified": "2025-04-01T08:15:47.001996Z",
"subcategory": "[REDACTED]",
"category": "Identity and Access Management",
"field_17": null,
"last_seen": "2025-04-01T08:15:47.002019Z",
"revoked": false,
"field_18": "Asset",
"created": "2025-03-27T02:18:06.501052Z",
"labels": {
"environment": null,
"deployment": null
},
"field_19": "[REDACTED]",
"first_seen": "2025-03-27T02:18:06.501069Z",
"field_20": null,
"status": "active",
"field_21": null,
"id": "[REDACTED]",
"description": null
}
You can extract portions of the JSON one demand, if you store it in a VARCHAR column. Search within json is quite fast, i would recommend starting there.
If the performance is not as good as you need, you could also flattening it into native columns, but that means extra processing and you might be good with just the JSON solution.