`parameter_value` in queryable (larger) JSON format

[suvayu]

• How much larger would the blobs be if we make them query friendly?

  • [ ] SQlite may support row based compression, check underlying principle, and if our datasets can take advantage, e.g. JSON keys are repeated across rows.
  • [ ] different queryable JSON formats
  • case 1 {'data': [['2024', 1.2], ['2025', 1.3]]}
  • case 2 {'data': [{'time': '2024', 'value': 1.2}, {'time': 2025': 'value': 1.3}]}
  • (extended case 1 for multi-index) {'data': [['scenario_1', '2024', 1.2], ['scenario_1', '2025', 1.3]]}
  • [ ] flatten JSON (unmap) and then query
  • how much faster?
  • how much larger? can we compress?
  • [ ] test cases
  • with index
  • without index

• Partial retrieval using JSON functions in SQL query

  • [ ] random read: jump to a location, read 1 row/block of rows
  • [ ] sequential (batched): moving window
  • [ ] filter/search: retrieve rows that meet a condition

Relevant info

parameter_value docs

Fallback (early)

If implementing JSON query with spinedb_api is too complicated, do an equivalent comparison using raw SQL or sqlalchemy (if supported).

NOTE: This is still in Python, and not in the SQLite CLI.

SQL dialects

• [ ] SQLite
• [ ] MySQL
• [ ] PostgreSQL (not really a priority for Spine :open_mouth:)