How to delete key inside object inside jsonb array in SQL?

Solution for How to delete key inside object inside jsonb array in SQL?
is Given Below:

I have the following json data in the foods column of my table:

[
  {
    "name": "Pasta",
    "price": 45.8,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Risotto",
    "price": 31.4,
  },
  {
    "name": "Pizza",
    "price": 64.9,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Hamburguer",
    "price": 14.9,
    "comments": {
      "combo": true
    }
  },
]

And I would like to delete all the promo keys, to look like this.

[
  {
    "name": "Pasta",
    "price": 45.8,
    "comments": {
      "special": true
    }
  },
  {
    "name": "Risotto",
    "price": 31.4,
  },
  {
    "name": "Pizza",
    "price": 64.9,
    "comments": {
      "special": true
    }
  },
  {
    "name": "Hamburguer",
    "price": 14.9,
    "comments": {
      "combo": true
    }
  },
]

This data may not make much sense because it’s an example to create to show the case I’m trying to solve, but with much more data, in which I need to delete a key that exists in some objects (not all, as in the example) that are inside of an jsonb array.
How can I best perform? I use PostgreSQL version 12, so preferably with jsonb functions.

even with jsonb operators you’ll need to unnest and reaggregate data, this seems to work:

create table receipts (
 receipt int,
 foods jsonb
);

insert into receipts values
(1, '[
  {
    "name": "Pasta",
    "price": 45.8,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Risotto",
    "price": 31.4
  },
  {
    "name": "Pizza",
    "price": 64.9,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Hamburguer",
    "price": 14.9,
    "comments": {
      "combo": true
    }
  }
]');
SELECT receipt, jsonb_agg(jsonb_strip_nulls(jsonb_set(element, '{"comments","promo"}', 'null')) ORDER BY ordinality) 
 FROM receipts
 JOIN LATERAL jsonb_array_elements(foods) WITH ORDINALITY AS el(element, ordinality)
   ON receipt = 1
GROUP BY receipt

.. but it will strip all null values (if any)

How to delete key inside object inside jsonb array in SQL?

Solution for How to delete key inside object inside jsonb array in SQL?
is Given Below:

I have the following json data in the foods column of my table:

[
  {
    "name": "Pasta",
    "price": 45.8,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Risotto",
    "price": 31.4,
  },
  {
    "name": "Pizza",
    "price": 64.9,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Hamburguer",
    "price": 14.9,
    "comments": {
      "combo": true
    }
  },
]

And I would like to delete all the promo keys, to look like this.

[
  {
    "name": "Pasta",
    "price": 45.8,
    "comments": {
      "special": true
    }
  },
  {
    "name": "Risotto",
    "price": 31.4,
  },
  {
    "name": "Pizza",
    "price": 64.9,
    "comments": {
      "special": true
    }
  },
  {
    "name": "Hamburguer",
    "price": 14.9,
    "comments": {
      "combo": true
    }
  },
]

This data may not make much sense because it’s an example to create to show the case I’m trying to solve, but with much more data, in which I need to delete a key that exists in some objects (not all, as in the example) that are inside of an jsonb array.
How can I best perform? I use PostgreSQL version 12, so preferably with jsonb functions.

even with jsonb operators you’ll need to unnest and reaggregate data, this seems to work:

create table receipts (
 receipt int,
 foods jsonb
);

insert into receipts values
(1, '[
  {
    "name": "Pasta",
    "price": 45.8,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Risotto",
    "price": 31.4
  },
  {
    "name": "Pizza",
    "price": 64.9,
    "comments": {
      "promo": true,
      "special": true
    }
  },
  {
    "name": "Hamburguer",
    "price": 14.9,
    "comments": {
      "combo": true
    }
  }
]');
SELECT receipt, jsonb_agg(jsonb_strip_nulls(jsonb_set(element, '{"comments","promo"}', 'null')) ORDER BY ordinality) 
 FROM receipts
 JOIN LATERAL jsonb_array_elements(foods) WITH ORDINALITY AS el(element, ordinality)
   ON receipt = 1
GROUP BY receipt

.. but it will strip all null values (if any)