PostgreSQL - Store JSON Object to Postgresql Table

Question

I have a table with type varchar[]

Field Name	Field Type
id	int
data	varchar[][]
insert_date	timestamps

I have a data json object

{
 { 
  name : "Mr. A",
  class: 
  {
    class_id: 1,
    exam: 95,
  },
  {
    class_id: 2,
    exam: 78,
  }
 },
 { 
  name : "Mr. B",
  class: 
  {
    class_id: 1,
    exam: 87,
  },
  {
    class_id: 2,
    exam: 87,
  }
 }
}

how do i insert the data into 1 row?

De-normalizing using arrays (varchar[]) is pretty much always a bad idea. But it's totally unclear to me what exactly you are trying to store into the table. I can't relate any of the JSON keys to the columns in your table. Why don't you just store the JSON value into a jsonb column? — user330315
– user330315, Commented Aug 25, 2021 at 5:31

Satyanand bhardwaj · Accepted Answer · 2021-08-25 06:08:27Z

1

You should be use jsonb to store JSON data in your table.

CREATE TABLE customer {
  contact JSONB
}

Example(Object) :- Insert Object JSON Value

Insert into customer(contact )
Values('{ "phones":[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ] }')

Example(Array) :- Insert array JSON Value

Insert into customer(contact )
Values('[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ]')

answered Aug 25, 2021 at 6:08

Satyanand bhardwaj

3653 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - Store JSON Object to Postgresql Table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related