slavyan887

Untitled

Oct 18th, 2021
2,014
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE country(
  2.     id text,
  3.     name text,
  4.     path text,
  5.     fullkey text
  6. );
  7.  
  8. CREATE TABLE area(
  9.     id text,
  10.     country_id text,
  11.     name text,
  12.     path text,
  13.     fullkey text
  14. );
  15.  
  16. CREATE TABLE city(
  17.     id text PRIMARY KEY,
  18.     area_id text,
  19.     name text,
  20.     path text,
  21.     fullkey text
  22. );
  23.  
  24. INSERT INTO country (id, name, path, fullkey)
  25. SELECT
  26.   json_extract(VALUE, "$.id") AS id,
  27.   json_extract(VALUE, "$.name") AS name,
  28.   path,
  29.   fullkey
  30. FROM json_each(readfile("area.json"));
  31.  
  32. INSERT INTO area
  33.   (id, name, path, fullkey)
  34. SELECT
  35.   json_extract(VALUE, '$.id') AS id,
  36.   json_extract(VALUE, '$.name') AS name,
  37.   REPLACE(path, '.areas', '') AS path,
  38.   fullkey
  39. FROM json_tree(readfile('area.json'))
  40. WHERE path LIKE '%.areas';
  41.  
  42. UPDATE area
  43. SET
  44.   country_id = (SELECT id FROM country WHERE country.fullkey = area.path)
  45. ;
  46.  
  47. INSERT INTO city
  48.   (id, name, path, fullkey)
  49. SELECT
  50.   json_extract(VALUE, '$.id') AS id,
  51.   json_extract(VALUE, '$.name') AS name,
  52.   REPLACE(path, '.areas', '') AS path,
  53.   fullkey
  54. FROM json_tree(readfile('area.json'))
  55. WHERE path LIKE '%.areas';
  56.  
  57. UPDATE city
  58. SET
  59.   area_id = (SELECT id FROM area WHERE area.fullkey = city.path)
  60. ;
  61.  
  62. SELECT
  63.   city.id,
  64.   area.name AS area,
  65.   country.name AS country,
  66.   city.name AS city
  67. FROM
  68.   country
  69.   JOIN area ON area.country_id = country.id
  70.   JOIN city ON city.area_id = area.id  
  71. WHERE
  72.   country = 'Россия' AND area = 'Самарская область'
  73. ;
RAW Paste Data