How to Insert JSON Array into MySQL Database

How to insert json array into mysql database

 $json = file_get_contents('php://input');
 $obj = json_decode($json,true);

I think you are passing the wrong variable. You should pass $json in json_decode as shown above.

how to insert json array into mysql using .net core

results.StudentResults is not of string type and your DB framework attempt to convert it to string, usually by using ToString() method available in all objects. You have to tell the program to convert it into JSON, see How to serialize and deserialize (marshal and unmarshal) JSON in .NET:

mycommand.Parameters.AddWithValue(
    "@StudentResults",
    JsonSerializer.Serialize(results.StudentResults));

System.Collections.Generic.List`1 text is the default ToString behavior when you don't implement one yourself.

insert a json array in a mysql table

Per the comments above, the answer is to use JSON.stringify to convert a Javascript object into a JSON string.

Also use SQL query parameters. Once you get the hang of it, it's actually easier than doing string-concatenation and getting eyestrain trying to balance all the quotes.

con1.query("INSERT INTO webhook_log SET tipo=?, riposta=?, created_on=now()",
  [eventType, JSON.stringify(data)],
  function(err,result){ if(err) throw err; console.log(🔔  Webhook logged); });

How to insert a string array into mysql json column

JSON documents have specific rules about format. String values inside a JSON document must be delimited with double-quotes, not single-quotes.

WRONG:

"['item1','item2','item3']"

RIGHT:

'["item1","item2","item3"]'

This works nicely inside SQL strings, because SQL strings should be delimited with single-quotes. MySQL is non-standard in supporting double-quotes as an alternative string delimiter.

To ensure creating a JSON document with correct format, you might like to use the JSON_ARRAY() or JSON_OBJECT() functions. You give arguments as individual values, and the function will return a document value, guaranteed to be formatted as valid JSON.

INSERT INTO table (json_column) 
VALUES (JSON_ARRAY('item1', 'item2', 'item3'));

Insert JSON in multiple array data to MySQL using PHP

You need to use nested loop for getting all the values like this and try to understand the use of foreach . foreach is used to loop through the array to get the value until N'th values .

Foreach reference

    foreach ($array["allRoundData"] as $row) 
    {

        $name = $row["name"];
        $time = $row['timeLimitInSeconds'];
        $points = $row['pointsAddedForCorrectAnswer'];

       foreach($row['questions'] as $row1)
       {    
            $question= $row1['questionText'];

            foreach($row1['answers'] as $row2)
            {

                $answer = $row2['answerText'];

                $isCorrect= $row2['isCorrect'];

                echo "INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('". $name."',".$time.",".$points.",'". $question."','". $answer ."','".  $isCorrect."') </br>";  
            }

       }

    }

OUTPUT :

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Lions are carnivores: true or false?','True','1') 

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Lions are carnivores: true or false?','False','') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'What do frogs eat?','Pizza','') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'What do frogs eat?','Flies','1') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','In the sea','') 

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','On the moon','') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','On land','1') 

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','In a tree','') 

SQL Insert JSON into table column

You can pass your data as a string, as long as it is valid JSON; MySQL will happily convert it for you under the hood.

insert into mytable (id, name, address)
values (
    1,
    'User name',
    '[{"street": "street address", "city": "Berlin"}]'
);

An alternative is to use JSON builder functions:

insert into mytable (id, name, address)
values (
    1,
    'User name',
    json_array(json_object('street', 'street address', 'city', 'Berlin'))
);

how to insert json into a json array in mysql?

Use this:

$query="update example set json_data=JSON_ARRAY_INSERT(json_data,'$[1]',
    CAST(:new_json_data AS JSON)) where id=3";

This will force the string input to be converted to a JSON document before appending it to the array.

Treating JSON array as a String to insert into MYSQL

To all those concerned: The problem was the " string. mysql, and probably SQL in general, don't handle these well. There are two options. The first, and less recommended:
String parsed = json.get(key).toString().replace("\"", "");
Then, insert normally.
The second, much more recommended - use PreparedStatement. This way, you can write an insert/update query without minding the escape characters. Example, after i've inserted 'null' values for the clothes column:

            PreparedStatement ps = conn.prepareStatement("UPDATE ORDERS set CLOTHES = ?  WHERE ID = ?");
            ps.setString(1, clothes); // clothes - get(key).toString()
            ps.setInt(2, count); // count - counts which iteration we are
            ps.executeUpdate();

I hope anyone in the future finds this useful.

---

Related Topics