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

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"}]');

CodePudding user response：

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 || '....'

CodePudding user response：

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

For Example:

var data = { Id : Id Name : Name }

Json Request :

Data: data

CodePudding user response：

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