将json插入Postgres中表的字段数组中

Question

PHP returns me the json:

{          
      "date"    : "2014-02-06",
      "typeDate": "local",
      "events"  : [
                    "To get know how Sherlock survived",
                    "Find who killed the President Kennedy",
                    "Cook met"
                  ]
       }

I need to insert this object into table's field which has the array of such objects:

{
     "dates": [   
     {          
      "date"    : "2014-02-06",
      "typeDate": "local",
      "events"  : [
                "Kill John Connor",
                "Kill T-1000",
                "Listen Moon sonata"
              ]
     },
     {        
      "date"    : "2014-02-07",
      "typeDate": "local",
      "events"  : [
                "Fix Enstain's theory",
                "Take pizza"
              ]
       }
   ]
}

How to do this correct ? Trying to use the example

INSERT INTO aa VALUES (1, 'my_json_record');

I'm getting the error about structure of json.

Answer 1

hbasically you soould serialize the JSON object before inserting into the database.

 $my_json_var = json_decode($my_json_record)

This methond takes a JSON encoded string and converts it into a PHP variable. see http://us2.php.net/json_decode

Then build your query...

 $sql = "INSERT INTO aa (field1, field2) VALUES (1, '$my_json_var')";