Currenty the database is initialised as stated here https://www.home-assistant.io/docs/backend/database/.
Some nested fields, i.e. events.event_data
, states.attributes
are stored as TEXT. These fields could be initiated as JSON when writing to JSON-compatible databases such as postgresql.
This allows direct querying, no hacky transformation needed when backing up and immediate access to json data, for the purpose of backing up, analysing (machine learning), or even sending to the cloud to compatible databases such as BigQuery.
Migration could be added to migrate data from TEXT to JSON.