So far, it's fast enough for me. If I ever need more performance, I guess I'll figure it out.
Couldn't Indexes on Expressions be used to index JSON fields?
Couldn't Indexes on Expressions be used to index JSON fields?
Since you already have it working sufficiently using SQLite, why would you switch?
SELECT
cities.data ->> '$.name' AS city_name,
countries.data ->> '$.name' AS country_name
FROM
cities
JOIN
countries ON cities.data -> '$.country_id' = countries.data -> '$.id';
It could be much smaller if the DB knew that all my tables are of the "single data column with JSON" form.