SQLite doesn't support indexing JSON (yet) unless you use generated columns. So performance-wise SQLite would probably not be the best option if that's all you do.
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';