How to add new key value pair to a field which is defined as JSON in PostgreSQL?

Question

I have created a table with 3 fields:

id varchar(100) Primary key,
name varchar(1000) NOT NULL,
details json

I have inserted 1 value to the details as

{ 
"age" 10,
"class": "4th",
"sport": "TT"
}

now I wanted to add a new key-value to this details field.

marks: 73

so final result should be:

{ 
"age" 10,
"class": "4th",
"sport": "TT",
"marks": 73
}

I have tried this query:

UPDATE studentable set details = details || '{"marks": 73"}' where id = 1

GEtting ERROR: column details is of type json but expression is of type text.

I have tried removing ' from the '{"marks": 73"}' then it gives

error: syntax error at or near "{"

Answer 1

The error says that you try to concatenate a json object with a text. So you just have to cast your text as a json value :

UPDATE studentable set details = details || '{"marks": 73"}' :: json where id = 1

Then, as already commented, you should use the jsonb type instead of json.