Mysql: how to store json data and still avoid duplication?

Question

MySQL

I'm working with a database of hotels via API, now I'm busy caching static information.
Actually the question is how to store all the data
Here is an example of information about the hotel

{
 "gen_timestamp": 1381489054.0071,
 "hotels": [
 {
 "id": "1058790",
 "cityId": "895",
 "stars": "2",
 "pricefrom": null,
 "rating": "0.0",
 "popularity": "1150",
 "propertyType": "",
 "checkOut": "",
 "checkIn": "",
 "distance": "1.00",
 "photoCount": "3",
 "photos": [
 {
 "url": "http://photo.hotellook.com/image_v2/original/151594987.jpg",
 "width": "298",
 "height": "240"
 },
 {
 "url": "http://photo.hotellook.com/image_v2/original/682702387.jpg",
 "width": "320",
 "height": "240"
 },
 {
 "url": "http://photo.hotellook.com/image_v2/original/700461773.jpg",
 "width": "320",
 "height": "239"
 }
 ],
 "facilities": [
 "22",
 "7",
 "43",
 "3",
 "41",
 "14",
 "9",
 "83",
 "25",
 "13",
 "56",
 "80",
 "37"
 ],
 "shortFacilities": [
 "restaurant",
 "parking",
 "laundry",
 "internet",
 "pool"
 ],
 "location": {
 "lat": "6.256917",
 "lon": "81.236275"
 },
 "name": {
 "en": "Wila Safari"
 },
 "address": {
 "en": "WEERAWILA"
 },
 "link": "/LK/Weerawila-895/Wila_Safari-1058790.html"
 }
 ]
}

I receive this information on the case of a request for a specific "place". conditionally - "city".
First, I did how - I created a table for hotels where I entered everything from the example above, except for what looked like a set - i.e. photo, facilities, shortFacilities - these tables looked something like this
catid, id, name(type), cityid
catid - auto-increment so that each entry has a unique number
id - belonging to the hotel
name(type) - depending on what kind of table - number or name
cityid - I introduced this variable so that when updating the subsequent city, it would be possible to delete data by making one request before writing new ones.
And actually, I ran into the following problem. - for example, I make a request for "city" - Goa with id 395
- and the query will return hotels not only "cityId": "395", but also many others, since Goa is not a specific place, but a state. and for each of these "other" cities to make a request - there hotels in the search results will be duplicated
, so it turned out that, firstly, in my scheme, data on belonging to different places of one hotel will not be taken into account.
and it turns out that when making a request to a location with a specific ID, I cannot simply delete hotels and photos from the database by this ID in order to enter new data.
for the main hotel table, REPLACE can be used, but then I won’t get belongings to different "cities" anywhere, and a table with photos, facilities, shortFacilities - in order to clear them, I will need to make a separate DELETE request for each hotel.
Well, there are a lot of cities - more than 30 thousand. many hotels have a lot of hotels, so the process of obtaining and updating data should be as quick and simple as possible. otherwise, the server will not digest so much information in a day.

Answer 1

Maybe it makes sense to use a NoSQL database like MongoDB? Just add (update) hotels (json) to the collection, and then use the aggregation framework to make selections.