PostgreSQL query to extract specific values from a JSONB column

Transact-SQL
1

I am working on creating a PostgreSQL query to address the following scenarios:

  • I have a column with a jsonb data type.
  • Within this column, there are 4 attributes, two of them are numbers and the other 2 are arrays containing objects with two properties: value and description.
  • For example:
"info_column": {
"attribute1": 10, 
"attribute2": 50, 
"attribute3": [
{"value": 10, "description": "desc1"},
{"value": 20, "description": "desc2"},
{"value": 30, "description": "desc3"}
], 
"attribute4": [
{"value": 40, "description": "desc4"},
{"value": 50, "description": "desc5"},
{"value": 60, "description": "desc6"}
]
}
  • The objective is to get the sum of attribute1, attribute2, attribute3 and attribute4 for every register.
    • In the case of attribute1and attribute2, the sum will the number they show as its attribute value.
    • In the other cases, the sum will correspond to the sum of every value in all objects in its attribute array.
  • The desired output for the data above would look something like this:
    |  attribute1| attribute2| attribute3| attribute4
row1|          10|         50|         60|        150

I would highly appreciate any assistance you could provide on this matter.