How to aggregate data from JSONB?

Question

PostgreSQL

There are jsonbs (see screenshot). The keys are not known in advance. The key values ​​are jsonb, in which the data of interest is stored in status. It can take the values FINISHED​​, PENDING, FAILURE.

Single row data example:

{
    "79361fa7-4d71-49f0-a035-a0355e4fd280_1": {
        "retry": 10,
        "status": "FINISHED",
        "interval": "2017-10-01 - 2017-11-01"
    },
    "79361fa7-4d71-49f0-a035-a0355e4fd280_2": {
        "retry": 10,
        "status": "PENDING",
        "interval": "2017-10-01 - 2017-11-01"
    },
    "79361fa7-4d71-49f0-a035-a0355e4fd280_3": {
        "retry": 10,
        "status": "FINISHED",
        "interval": "2017-10-01 - 2017-11-01"
    }
}

It is required to calculate how much of what and in what quantity. That is, for example, FINISHED: 5, FAILURE: 1, and so on.

Please advise

Answer 1

SELECT t.value->>'status' AS status, count(t.value->>'status')
FROM table_name, jsonb_each(table_name.jsonb_column_name) AS t
GROUP BY status