CODEWITHSUNDEEP
jsonpostgresqljsonb
blue_man
blue_man

Reputation: 63

How do I selectively filter and aggregate jsonb in Postgres

{
  "timeStamp": 1593664441878,
  "timingRecords": [
    {
      "task": "extendedClean",
      "time": 31,
      "modules": [
        "main"
      ]
    },
    {
      "task": "clean",
      "time": 35,
      "modules": [
        "lint"
      ]
    },
        {
      "task": "compile",
      "time": 35,
      "modules": [
        "test"
      ]
    }
  ]
}

This is my json data in the table. I have multiple rows of similar records.

I am looking for a result as the sum of all times where task in (extendedClean, clean)

So my final expected result would look like

timestamp    | sum(time)
1593664441878| 66
1593664741878| 22

Upvotes: 0

Views: 328

Answers (1)

user330315
user330315

Reputation:

It's a bit unclear how you need that in the context of a complete query. But given a single JSON value as shown in your question, you can do this:

select sum( (e ->> 'time')::int )
from the_table
  cross join jsonb_array_elements(the_json_column -> 'timingRecords') as e
where e ->> 'task' in ('extendedClean', 'clean');

Online example

Upvotes: 1

Related Questions