The “right” tool for the job depends on a number of factors. Performance is one of them, but ease of implementation is just as important depending on the scale.
Since you already have it working sufficiently using SQLite, why would you switch?
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.