mysql

[SOLVED] Insert multiple result JSON data into MySQL

Issue

I would like to insert the JSON data into MySQL database in the very obvious way as given an example in the picture. Picture shows the same data, but outputed in CSV format and that’s how exactly I want it to be in MySQL.

I have prepared the database and the query for inserting, however I can’t figure out the code to find right array key or value

...  prepared query
// INSERT INTO `utakmice`(`utakmica_name`, `utakmica_kladionica_name`, `utakmica_kladionica_kvota1`, `utakmica_kladionica_kvotax`, `utakmica_kladionica_kvota2`, `utakmica_kladionica_kvota3plus`, `utakmica_kladionica_kvota0_2`) VALUES ([value], [value], [value], [value], [value], [value], [value])

$json_array = json_decode($data, true);

foreach($json_array as $key => $arrays){

    foreach($arrays as $array){
        foreach($array as $key => $value){
            echo $key . " => " . $value . "<br />";
        }
    }
    echo "<br />";
}

See picture example

JSON:

{
 "utakmica": [
  {
   "name": "Kimarones de Sonora-Klub Selaja",
   "kladionica": [
    {
     "name": "Admiral Bet",
     "kvota1": "2.05",
     "kvotax": "3.00",
     "kvota2": "3.70",
     "kvota3plus": "2.60"
    },
    {
     "name": "Balkan Bet",
     "kvota1": "2.15",
     "kvotax": "2.80",
     "kvota2": "3.65",
     "kvota0_2": "1.42",
     "kvota3plus": "2.65"
    },
    {
     "name": "BetOle",
     "kvota1": "2.02",
     "kvotax": "2.93",
     "kvota2": "3.95",
     "kvota0_2": "1.40",
     "kvota3plus": "2.72"
    },
    {
     "name": "Lob",
     "kvota1": "2.05",
     "kvotax": "2.95",
     "kvota2": "3.80",
     "kvota0_2": "1.41",
     "kvota3plus": "2.60"
    },
    {
     "name": "MaxBet",
     "kvota1": "2.20",
     "kvotax": "2.90",
     "kvota2": "3.45",
     "kvota0_2": "1.45",
     "kvota3plus": "2.50"
    },
    {
     "name": "Meridian",
     "kvota1": "2.17",
     "kvotax": "2.85",
     "kvota2": "3.60",
     "kvota0_2": "1.45",
     "kvota3plus": "2.65"
    },
    {
     "name": "MerkurXtip",
     "kvota1": "2.20",
     "kvotax": "2.75",
     "kvota2": "3.40",
     "kvota0_2": "1.43",
     "kvota3plus": "2.50"
    },
    {
     "name": "Mozzart",
     "kvota1": "2.15",
     "kvotax": "3.00",
     "kvota2": "3.50",
     "kvota0_2": "1.47",
     "kvota3plus": "2.50"
    },
    {
     "name": "Oktagonbet",
     "kvota1": "2.30",
     "kvotax": "2.80",
     "kvota2": "3.30",
     "kvota0_2": "1.40",
     "kvota3plus": "2.70"
    },
    {
     "name": "Olimp",
     "kvota1": "2.15",
     "kvotax": "2.90",
     "kvota2": "3.45",
     "kvota0_2": "1.50",
     "kvota3plus": "2.30"
    },
    {
     "name": "Taš-Bet",
     "kvota1": "2.20",
     "kvotax": "2.80",
     "kvota2": "3.40",
     "kvota0_2": "1.47",
     "kvota3plus": "2.60"
    },
    {
     "name": "Top Bet",
     "kvota1": "2.30",
     "kvotax": "2.85",
     "kvota2": "3.10",
     "kvota0_2": "1.45",
     "kvota3plus": "2.55"
    }
   ]
  },
  {
   "name": "Al Naser-Al Šabab Kuvajt",
   "kladionica": [
    {
     "name": "Admiral Bet",
     "kvota1": "1.83",
     "kvota2": "4.10",
     "kvota0_2": "1.72",
     "kvota3plus": "2.00"
    },
    {
     "name": "Balkan Bet",
     "kvota1": "1.77",
     "kvotax": "3.25",
     "kvota2": "4.00",
     "kvota0_2": "1.65",
     "kvota3plus": "2.00"
    },
    {
     "name": "Lob",
     "kvota1": "1.89",
     "kvotax": "2.95",
     "kvota2": "4.15",
     "kvota0_2": "1.62",
     "kvota3plus": "2.08"
    },
    {
     "name": "MaxBet",
     "kvota1": "1.83",
     "kvotax": "3.15",
     "kvota2": "4.10",
     "kvota0_2": "1.70",
     "kvota3plus": "1.95"
    },
    {
     "name": "Meridian",
     "kvota1": "1.82",
     "kvotax": "3.40",
     "kvota2": "4.00",
     "kvota0_2": "1.71",
     "kvota3plus": "2.02"
    },
    {
     "name": "Mozzart",
     "kvota1": "1.92",
     "kvotax": "3.35",
     "kvota2": "3.80",
     "kvota0_2": "1.72",
     "kvota3plus": "2.00"
    }
   ]
  },
  {
   "name": "Al Rustak-Al Sib",
   "kladionica": [
    {
     "name": "Admiral Bet",
     "kvota1": "3.70",
     "kvota2": "1.95",
     "kvota0_2": "1.48",
     "kvota3plus": "2.35"
    },
    {
     "name": "Lob",
     "kvota1": "3.80",
     "kvotax": "3.10",
     "kvota2": "2.08",
     "kvota0_2": "1.47",
     "kvota3plus": "2.45"
    },
    {
     "name": "MaxBet",
     "kvota1": "3.55",
     "kvotax": "3.10",
     "kvota2": "1.88",
     "kvota0_2": "1.48",
     "kvota3plus": "2.35"
    },
    {
     "name": "Meridian",
     "kvota1": "3.85",
     "kvotax": "3.05",
     "kvota2": "1.98",
     "kvota0_2": "1.48",
     "kvota3plus": "2.41"
    },
    {
     "name": "Mozzart",
     "kvota1": "4.00",
     "kvotax": "3.05",
     "kvota2": "2.00",
     "kvota0_2": "1.47",
     "kvota3plus": "2.50"
    }
   ]
  },
  {
   "name": "El Šarkeja-Talaea El Džaiš",
   "kladionica": [
    {
     "name": "Admiral Bet",
     "kvota1": "3.60",
     "kvota2": "2.15",
     "kvota0_2": "1.53",
     "kvota3plus": "2.45"
    },
    {
     "name": "Balkan Bet",
     "kvota1": "3.50",
     "kvotax": "2.80",
     "kvota2": "2.20",
     "kvota0_2": "1.47",
     "kvota3plus": "2.50"
    },
    {
     "name": "BetOle",
     "kvota1": "3.60",
     "kvotax": "2.85",
     "kvota2": "2.15",
     "kvota0_2": "1.50",
     "kvota3plus": "2.43"
    },
    {
     "name": "Lob",
     "kvota1": "3.50",
     "kvotax": "2.85",
     "kvota2": "2.12",
     "kvota0_2": "1.47",
     "kvota3plus": "2.38"
    },
    {
     "name": "MaxBet",
     "kvota1": "3.55",
     "kvotax": "2.95",
     "kvota2": "2.15",
     "kvota0_2": "1.50",
     "kvota3plus": "2.40"
    },
    {
     "name": "Meridian",
     "kvota1": "3.95",
     "kvotax": "2.90",
     "kvota2": "2.12",
     "kvota0_2": "1.49",
     "kvota3plus": "2.60"
    },
    {
     "name": "MerkurXtip",
     "kvota1": "3.65",
     "kvotax": "2.72",
     "kvota2": "2.23",
     "kvota0_2": "1.48",
     "kvota3plus": "2.40"
    },
    {
     "name": "Mozzart",
     "kvota1": "3.60",
     "kvotax": "2.95",
     "kvota2": "2.15",
     "kvota0_2": "1.41",
     "kvota3plus": "2.70"
    },
    {
     "name": "Oktagonbet",
     "kvota1": "3.60",
     "kvotax": "2.80",
     "kvota2": "2.18",
     "kvota0_2": "1.50",
     "kvota3plus": "2.40"
    },
    {
     "name": "Olimp",
     "kvota1": "3.80",
     "kvotax": "2.95",
     "kvota2": "2.10",
     "kvota0_2": "1.45",
     "kvota3plus": "2.50"
    },
    {
     "name": "Taš-Bet",
     "kvota1": "3.50",
     "kvotax": "2.90",
     "kvota2": "2.15",
     "kvota0_2": "1.50",
     "kvota3plus": "2.45"
    },
    {
     "name": "Top Bet",
     "kvota1": "3.00",
     "kvotax": "3.10",
     "kvota2": "2.20",
     "kvota0_2": "1.50",
     "kvota3plus": "2.40"
    }
   ]
  }
 ]
}

MySQL create table looks like such;

CREATE TABLE `utakmice` (
  `utakmica_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `utakmica_kladionica_name` varchar(64) NOT NULL,
  `utakmica_kladionica_kvota1` decimal(10,0) NOT NULL,
  `utakmica_kladionica_kvotax` decimal(10,0) NOT NULL,
  `utakmica_kladionica_kvota2` decimal(10,0) NOT NULL,
  `utakmica_kladionica_kvota3plus` decimal(10,0) NOT NULL,
  `utakmica_kladionica_kvota0_2` decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Current code


  $parsehub = new Parsehub($api_key);
    $data =  $parsehub->getLastReadyRunData($project_token);

    $stmt = $mysqli->prepare("INSERT INTO utakmice
    SELECT jsontable1.utakmica_name, 
           jsontable2.*
    FROM ( SELECT ? AS source_JSON ) AS data_source
    CROSS JOIN JSON_TABLE(data_source.source_JSON,
                          '$.utakmica[*]' 
                          COLUMNS ( utakmica_name VARCHAR(64) PATH '$.name' ERROR ON EMPTY,
                                    kladionica JSON PATH '$.kladionica' ERROR ON EMPTY
                                   )
                          ) jsontable1
    CROSS JOIN JSON_TABLE(jsontable1.kladionica,
                          '$[*]'
                          COLUMNS ( utakmica_kladionica_name VARCHAR(64) PATH '$.name' ERROR ON EMPTY,
                                    utakmica_kladionica_kvota1 DECIMAL(10,2) PATH '$.kvota1' DEFAULT '0' ON EMPTY,
                                    utakmica_kladionica_kvotax DECIMAL(10,2) PATH '$.kvotax' DEFAULT '0' ON EMPTY, 
                                    utakmica_kladionica_kvota2 DECIMAL(10,2) PATH '$.kvota2' DEFAULT '0' ON EMPTY, 
                                    utakmica_kladionica_kvota3plus DECIMAL(10,2) PATH '$.kvota3plus' DEFAULT '0' ON EMPTY, 
                                    utakmica_kladionica_kvota0_2 DECIMAL(10,2) PATH  '$.kvota0_2' DEFAULT '0' ON EMPTY
                                   )
                          ) jsontable2;");

       $stmt->bind_param("s", $data);
       $stmt->execute();

Solution

INSERT INTO utakmice
SELECT jsontable1.utakmica_name, 
       jsontable2.*
FROM ( SELECT @input AS source_JSON ) AS data_source
CROSS JOIN JSON_TABLE(data_source.source_JSON,
                      '$.utakmica[*]' 
                      COLUMNS ( utakmica_name VARCHAR(64) PATH '$.name' ERROR ON EMPTY,
                                kladionica JSON PATH '$.kladionica' ERROR ON EMPTY
                               )
                      ) jsontable1
CROSS JOIN JSON_TABLE(jsontable1.kladionica,
                      '$[*]'
                      COLUMNS ( utakmica_kladionica_name VARCHAR(64) PATH '$.name' ERROR ON EMPTY,
                                utakmica_kladionica_kvota1 DECIMAL(10,2) PATH '$.kvota1' DEFAULT '0' ON EMPTY,
                                utakmica_kladionica_kvotax DECIMAL(10,2) PATH '$.kvotax' DEFAULT '0' ON EMPTY, 
                                utakmica_kladionica_kvota2 DECIMAL(10,2) PATH '$.kvota2' DEFAULT '0' ON EMPTY, 
                                utakmica_kladionica_kvota3plus DECIMAL(10,2) PATH '$.kvota3plus' DEFAULT '0' ON EMPTY, 
                                utakmica_kladionica_kvota0_2 DECIMAL(10,2) PATH  '$.kvota0_2' DEFAULT '0' ON EMPTY
                               )
                      ) jsontable2;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e8dd034010d9c6a65f3027d8e30eba6b

PS. The datatypes changed from DECIMAL(10,0) to DECIMAL(10,2) because the values contains 2 decimal digits.

PPS. The subquery data_source is added for a case when you want to provide a rowset of JSON values. If you’ll provide one JSON only then you may remove this subquery and use .. FROM JSON_TABLE(@input, .. (see last SELECT in the fiddle).

Answered By – Akina

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *