By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `odds_changes` (
`kladionica_name` varchar(64) NOT NULL,
`kladionica_utakmica` varchar(64) NOT NULL,
`kladionica_utakmica_url` varchar(256) NOT NULL,
`kladionica_kvota1` VARCHAR(10) NOT NULL,
`kladionica_kvotax` VARCHAR(10) NOT NULL,
`kladionica_kvota2` VARCHAR(10) NOT NULL,
`kladionica_kvota3plus` VARCHAR(10) NOT NULL,
`kladionica_kvota0_2` VARCHAR(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
SET @input :=
'
{
"kladionica": [
{
"name": "MerkurXtip",
"utakmica": "Herogejt Taun - Lejton Orijent",
"utakmica_url": "https://www.fudbal91.com/soccer_clubs/compare/Harrogate_Town_vs_Leyton_Orient",
"kvota1": "2.75\n2.72",
"kvotax": "3.25\n3.30",
"kvota2": "2.55\n2.57",
"kvota3plus": "2.33\n2.35"
},
{
"name": "Mozzart",
"utakmica": "Herogejt Taun - Lejton Orijent",
"utakmica_url": "https://www.fudbal91.com/soccer_clubs/compare/Harrogate_Town_vs_Leyton_Orient",
"kvota1": "2.90\n2.95",
"kvotax": "3.05\n3.00",
"kvota0_2": "1.60\n1.55",
"kvota3plus": "2.20\n2.30"
},
{
"name": "Lob",
"utakmica": "Danfermlajn Atl. - Partik Tistl",
"utakmica_url": "https://www.fudbal91.com/soccer_clubs/compare/Dunfermline_Athletic_vs_Partick_Thistle",
"kvota0_2": "1.49\n1.50"
},
{
"name": "Meridian",
"utakmica": "Danfermlajn Atl. - Partik Tistl",
"utakmica_url": "https://www.fudbal91.com/soccer_clubs/compare/Dunfermline_Athletic_vs_Partick_Thistle",
"kvota1": "2.60\n2.65",
"kvota2": "2.95\n2.85"
},
{
INSERT INTO odds_changes
SELECT jsontable1.kladionica_name,
jsontable1.kladionica_utakmica,
jsontable1.kladionica_utakmica_url,
jsontable1.kladionica_kvota1,
jsontable1.kladionica_kvotax,
jsontable1.kladionica_kvota2,
jsontable1.kladionica_kvota0_2,
jsontable1.kladionica_kvota3plus
FROM ( SELECT @input AS source_JSON ) AS data_source
CROSS JOIN JSON_TABLE(data_source.source_JSON,
'$[*]'
COLUMNS ( kladionica_name VARCHAR(64) PATH '$.name' ERROR ON EMPTY,
kladionica_utakmica VARCHAR(64) PATH '$.utakmica' ERROR ON EMPTY,
kladionica_utakmica_url VARCHAR(64) PATH '$.utakmica_url' ERROR ON EMPTY,
kladionica_kvota1 VARCHAR(64) PATH '$.kvota1' ERROR ON EMPTY,
kladionica_kvotax VARCHAR(64) PATH '$.kvotax' ERROR ON EMPTY,
kladionica_kvota2 VARCHAR(64) PATH '$.kvota2' ERROR ON EMPTY,
kladionica_kvota0_2 VARCHAR(64) PATH '$.kvota0_2' ERROR ON EMPTY,
kladionica_kvota3plus VARCHAR(64) PATH '$.kvota3plus' ERROR ON EMPTY
)
) jsontable1;
Invalid JSON text in argument 1 to function json_table: "Invalid encoding in string." at position 216.
SELECT * FROM odds_changes;