how to add new JSON Object in PostgreSQL in a text field

Question

I had a column in PostgreSQL which has text datatype (data text)

the data in this column will be like

[
 {"code":"ABC","Value":"A1"},
 {"code":"Name","Value":"Ramesh"},
 {"code":"Age","paramValue":"24"}
]

I need to add new parameter to it such as

{"code":"Mobile","paramValue":"9XXXXXXXXX"}

the data would be like

[
 {"code":"ABC","Value":"A1"},
 {"code":"Name","Value":"Ramesh"},
 {"code":"Age","paramValue":"24"},
 {"code":"Mobile","paramValue":"9XXXXXXXXX"}
]

I tried many ways but I couldn't succeed can i get suggestion on this

Welcome to SO. Could you also add these attempts you mentioned to the question? — Jim Jones
– Jim Jones, Commented May 27, 2021 at 14:23
am sorry i am new PG first i tried reading json like SELECT id, data ::json->'"code":"ABC"' as name FROM my_table; i am getting error ERROR: invalid input syntax for type json DETAIL: Expected "," or "}", but found ":". — ramesh
– ramesh, Commented May 27, 2021 at 14:34

Akhilesh Mishra · Accepted Answer · 2021-05-27 14:45:42Z

1

Simply use || operator. Documentation

Considering your structure is always a valid JSON Array stored in text column.

Try This:

create table test (id int, json_field text);

insert into test values(1,'[{"code":"ABC","Value":"A1"},
{"code":"Name","Value":"Ramesh"},
{"code":"Age","paramValue":"24"}]');

Final Code

update test set json_field=
json_field::jsonb || '{"mobile":"kXXXXXXXX"}'
where id=1

DEMO

answered May 27, 2021 at 14:45

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

how to add new JSON Object in PostgreSQL in a text field

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