Mysql – See if an JSON array in MySQL contains an object whose key holds a specific date

Do not have 4100 identical tables. There are so many issues with such, and it is repeatedly panned in this and other forums.

4100 "stations", each with, say 10 readings, per hour? 41000 is about 12/second (average), which is not very much if you had one row per datapoint per station. One table with 3 columns: hour (or datetime), station_id, reading. And batch the 10 rows in a single INSERT statement for a little more efficiency.

How you can fetch the data in a variety of ways -- "just temperatures at 6am on weekends" becomes a simple, and somewhat efficient query.

Alternatively, have one table with 12 columns: hour/datetime, station_id, and 10 specific readings. This is likely to be more efficient in all ways. This also has the advantage that the readings can have specific datatypes for each measurement. Temperature or humidity: TINYINT SIGNED is 1 byte and allows -128..127 or DECIMAL(5.2) (3 bytes) for +/-999.99 instead of a generic FLOAT (4 bytes).

The alternatives probably take a similar amount of disk space. This database will be rather big after a year; you should probably estimate the future size and think ahead about indexing, and other issues.

I chose to go with JSON since item data that blacksmith produces is just informative, and does not have any specific value other than finding the specific blacksmith by his skills

I find this paradoxical- searching by a multi-valued field IS one of the reasons to apply normalization, as it simplifies a lot the searches. You can do what you mention, but without extra information that justifies that decision, that is not the simplest approach from a pure relationship model. Because you say:

Please suggest your own structures you think would be most effective.

I will first suggest how I would do it:

Normalized schema

CREATE TABLE blacksmiths (
  blacksmith_id int unsigned auto_increment PRIMARY KEY,
  blacksmith_name varchar(300) NOT NULL,
  blacksmith_lvl int unsigned NOT NULL
);

CREATE TABLE items (
  item_id int unsigned auto_increment PRIMARY KEY,
  item_name varchar(300) NOT NULL, # or level1, level2, level3?
)

CREATE TABLE blacksmith_items (
  item_id int unsigned,
  blacksmith_id int unsigned,
  level unsigned int,
  PRIMARY KEY(item_id, blacksmith_id),
  KEY(blacksmith_id),
  CONSTRAINT item_id_fk  (item_id) REFERENCES items (item_id),
  CONSTRAINT blacksmith_id_fk  (blacksmith_id) REFERENCES blacksmiths (blacksmith_id),
);

I don't get exactly how levels come into play- if it is a property of blacksmiths or items, but you can modify the join table to make it part of the primary key or not, and/or modify the indexes to search them faster. Or maybe the items columns have to be separated into metal, name and type, etc.

To get all items for blacksmith id1:

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1;

To get items with a specific level;

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1
AND blacksmith_items.level = 2;

Using joins is not really that complicated, and it will allow you more flexibility when you change the number of levels, or the number of items, and you can generate a json output from your application code.