CREATE TABLE country(
    id text,
    name text,
    path text,
    fullkey text
);

CREATE TABLE area(
    id text,
    country_id text,
    name text,
    path text,
    fullkey text
);

CREATE TABLE city(
    id text PRIMARY KEY,
    area_id text,
    name text,
    path text,
    fullkey text
);

INSERT INTO country (id, name, path, fullkey)
SELECT
  json_extract(VALUE, "$.id") AS id,
  json_extract(VALUE, "$.name") AS name,
  path,
  fullkey
FROM json_each(readfile("area.json"));

INSERT INTO area
  (id, name, path, fullkey)
SELECT
  json_extract(VALUE, '$.id') AS id,
  json_extract(VALUE, '$.name') AS name,
  REPLACE(path, '.areas', '') AS path,
  fullkey
FROM json_tree(readfile('area.json'))
WHERE path LIKE '%.areas';

UPDATE area
SET
  country_id = (SELECT id FROM country WHERE country.fullkey = area.path)
;

INSERT INTO city
  (id, name, path, fullkey)
SELECT
  json_extract(VALUE, '$.id') AS id,
  json_extract(VALUE, '$.name') AS name,
  REPLACE(path, '.areas', '') AS path,
  fullkey
FROM json_tree(readfile('area.json'))
WHERE path LIKE '%.areas';

UPDATE city
SET
  area_id = (SELECT id FROM area WHERE area.fullkey = city.path)
;

SELECT
  city.id,
  area.name AS area,
  country.name AS country,
  city.name AS city
FROM
  country
  JOIN area ON area.country_id = country.id
  JOIN city ON city.area_id = area.id
WHERE
  country = 'Россия' AND area = 'Самарская область'
;