I have a table Vehicle with column name car which is of JSONB type with the following value

{
  "car": {
    "petrol_type": [
      {
        "brand": "suzuki",
        "engine": "honda"
      },
      {
        "brand": "porshe",
        "engine": "merc"
      }
    ]
  }
}

I want to add another row to this column as

{
  "brand": "mclaren",
  "engine": "honda"
}

Can anyone please guide as to what the script would be for this.

CodePudding user response：

You can use pipes (||) as concatenation operators after splitting the innermost objects through use of JSONB_EACH() function such as

WITH j AS
(
 SELECT JSONB_BUILD_OBJECT( 'car',
          JSONB_BUILD_OBJECT(
                              'petrol_type',
                              j.value||
                              '{ "brand" : "mclaren", "engine" : "honda"}'::JSONB 
                            )
                          ) AS new_val 
   FROM vehicle,
        JSONB_EACH(car -> 'car') AS j 
)
UPDATE vehicle
   SET car = new_val 
  FROM j

Demo

CodePudding user response：

You can use jsonb_insert()

update vehicle 
   set car = jsonb_insert(car, 
                          '{car, petrol_type, -1}', 
                          '{"brand": "mclaren","engine": "honda"}', 
                          true)
from vehicle;

The -1 plus the true append the new value to the end of the array.