0

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();
Improve this question
0

1 Answer 1

Reset to default
1 
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).

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

I think the JSON_TABLE voodoo could use a bit more explanation for researchers that are new to this technique, but I like this answer enough to uv it.
@mickmackusa I think writing a tutorial on using a function doesn't make sense. In Reference Manual, all this is written quite well and in detail. And if the OP has any questions, then he can ask them, and I will explain what is unclear.
Okay, that's your prerogative. This is not a code-only answer, so I am happy. When I post a regex answer, I typically try to explain the pattern so that readers who are not fluent in regex can try to grasp what I've created. I understand what you are doing with $.utakmica[*] and $[*], I'm just trying to be mindful of people that don't follow the basic technique/syntax. Also, my stance is: the better explained this answer is, the more likely it will be a good page to close a future duplicate question.
@mickmackusa When you explain what your regex performs precisely then you assume that OP have some base knowledge about the regex syntax and usage. In this case I do not see what can be unclear if OP have base knowledge about JSON_TABLE function and JSON paths syntax. SQL language is self-descriptive, so the query text is self-explanation at the same time. I understand that some points can be unclear, and I wait for precise questions. If somebody (maybe OP) will ask then I, of course, will explain in details. Maybe, even I'll update the answer with additional question and my explanation.
The answer seems interesting, however I don't understand how to use it. Where should I declare the json file in the query? I've tried replacing FROM ( SELECT @input to FROM ( SELECT '$data', but the data isn't inserting. 2) Do I need to use these loops from my code?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.