Your Location is: Home > Postgresql-9.5

Change array's items on fly

From: Iceland View: 1636 a_subscriber 

Question

Suppose I has table with column "my_column" with type jsonb

Here json exmaple:

{
  "id": 1111111,
  "items": [
    {
      "qty" : 1,
      "amount": 2290,
      "discount": 9
    },
    {
      "qty" : 12,
      "amount": 100,
      "discount": 10
    }
  ]
}

The array's size may be 1 or more (e.g. 15) I need to return json and on "fly" change the amount = amount - discount

SELECT my_column ....

So the result must be json like this:

{
  "id": 1111111,
  "items": [
    {
     "qty" : 1,
      "amount": 2281,
      "discount": 9
    },
    {
      "qty" : 12,
      "amount": 90,
      "discount": 10
    }
  ]
}

Best answer