is there a way to insert json multiple times into the same record?

Question

Let's say I have inserted a record like this:

    id | u_id | the_data
-------+------+--------------------------------------------------
     1 | 2863 |[{name: a, body: lorem}]

using this command:

CREATE TABLE users (
  id  SERIAL PRIMARY KEY,
  u_id INT,
  the_data JSON
);


INSERT INTO users (u_id, the_data) VALUES (2863, '[{"name": "a", "body": "lorem"}]');

But now, I want to insert some more data into the same record without losing the old array of json. How to do this type of insertion?

    id | u_id | the_data
-------+------+------------------------------------------------------------------------------
     1 | 2863 |[ {name: a, body: lorem}, {name: b, body: ipsum} ]

Please note: Below command creates a new record which I don't want.

INSERT INTO users (u_id, the_data) 
VALUES (2863, '[{"name": "b", "body": "ipsum"}]');

Not looking for solutions like below since they all insert at the same time:

INSERT INTO users (u_id, the_data) 
VALUES (2863, '[{"name": "a", "body": "lorem"}, {"name": "b", "body": "ipsum"}]');
INSERT INTO users (u_id, the_data) 
VALUES (2863, '[{"name": "a", "body": "lorem"}]'), (2863, '[{"name": "b", "body": "ipsum"}]');

You are looking for an UPDATE statement combined with `jsonb_set()` (`jsonb` is the recommended data type to store JSON values) — , Aug 02 '22 at 09:39

score 1 · Answer 1 · answered Aug 02 '22 at 09:58

1

As the top level JSON object is an array, you can use the standard concatenation operator || to append an element to the array:

update users
  set the_data = the_data || '{"name": "b", "body": "ipsum"}'
where u_id = 2863;

You should change your column definition to jsonb as that offers a lot more possibilities for querying or changing the value. Otherwise you will be forced to cast the column to jsonb every time you want to do something more interesting with it.

If you can't or don't want to change the data type you need to cast it:

set the_data = the_data::jsonb || '....'

answered Aug 02 '22 at 09:58

I only changed the datatype to jsonb and ran the above command that you provided, but i get an `error: column "the_data" is of type jsonb but expression is of type text`
`LINE 1: update users set the_data = ^the_data || '{"name": "b", "body...`
`HINT: You will need to rewrite or cast the expression. ` . it shows `^` saying this is where the error is at line 1. – Deke Aug 02 '22 at 11:16
[Works for me](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ba446174aa20587a45677063495d81c8) – Aug 02 '22 at 11:22

score 0 · Answer 2 · answered Aug 02 '22 at 09:45

0

You can create list of object and parse it to loop :

For Example:

var data = { Id : Id Name : Name }

Json Request :

Data: data

answered Aug 02 '22 at 09:45

Shaikh Mustafa Ahmad

1
4

That isn't valid Postgres code (neither SQL nor PL/pgSQL) – Aug 02 '22 at 10:42
Would be possible to add a link to documentation? – Pedro Paiva Aug 04 '22 at 13:26

score 0 · Answer 3 · answered Aug 02 '22 at 10:20

Well, that's not a simple json object. You're trying to add a object to an array of values that is saved as json field.

So it's not about keeping the old array, but rather keeping the objects that were already present on the array saved in the json field and adding the new one.

I tried this on Postgres 12, it works, basically as someone else said, you will need to cast the jsonb type if you've json and use pipes operator to concatenate the new value.

    UPDATE users
    SET the_data = the_data::jsonb || '{"name": "b", "body": "ipsum"}'
    WHERE id = 1;

Taken from here: https://stackoverflow.com/a/69630521/9231145

is there a way to insert json multiple times into the same record?

3 Answers3