Insert json into array of table's field in 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.

can you post the exact error?

blotto
– blotto

2014-02-12 21:01:36 +00:00
Commented Feb 12, 2014 at 21:01 — blotto
– blotto, Commented Feb 12, 2014 at 21:01

Community · Accepted Answer · 2023-11-20 22:45:59Z

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 https://www.php.net/json_decode

Then build your query...

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

edited Nov 20, 2023 at 22:45

CommunityBot

11 silver badge

answered Feb 12, 2014 at 21:07

blotto

3,4071 gold badge22 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Insert json into array of table's field in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related