Currently my query is like this:

await pool.query("UPDATE data SET json_data = jsonb_set(json_data, '{1}', $1)",[req.body]);

In my table json_data column is like this (column type is jsonb):

{
  "0": {
    "id": "bbcdbb7c-7b81-432f-8401-e79e6d70a486",
    "name": "User name",
    "type": 0,
  },
  "1": {
    "id": "bbcdbb7c-7b81-432f-8401-e79e6d70a486",
    "name": "User name2",
    "type": 0,
  }
}

I want to insert element in index 1 and move the previous element in index 1 to 2. But in my query it updates the element in index 1 to new element. How can I insert new element between indexes and increase others index number?

CodePudding user response：

It is weird to have an object whose keys are all stringified integers. If you had stored this as an array rather than an object, then you could use the jsonb_insert function.

If you really want to do it with an object, then you will need to disassemble it and reassemble it. It would be awkward to code that from scratch inside larger statements, so it would be best to put it into a user defined function, such as:

create or replace function insert_object_like_array(jsonb,int,jsonb) returns jsonb language sql as $$
  select jsonb_object_agg(key, value) from (
      select case when key::int<$2 then key else (key::int 1)::text end key, value from jsonb_each($1) 
      union all 
      select $2::text,$3
  ) foobar;
$$;

Then in your example you would replace jsonb_set call with:

...json_data = insert_object_like_array(json_data, 1, $1)...

If you wanted to do this somewhere other than the top level, you could combine insert_object_like_array with jsonb_set, or maybe code the function differently to take a text[] path rather than an integer.

Or, since you tagged this question with javascript, maybe the most natural solution would be to read the whole jsonb value, manipulate it in javascript, then push it back.