Sum values extracted from a json array based on condition

Question

I have a JSON field in a table like below

{
"Ui": [
    {
        "element": "TG1",
        "mention": "in",
        "time": 123
    },
    {
        "element": "TG1",
        "mention": "out",
        "time": 125
    },        
    { "element": "TG2",
        "mention": "in",
        "time": 251
    },
    {
        "element": "TG2",
        "mention": "out",
        "time": 259
    }
]
 }

My intention is to get something like below

| element  |   Timespent   |
|  TG1     |     2         |
|  TG2     |     8         |

but have been completely unsuccessful. How would I get the difference (sum of all time when mention is in - sum of all time when mention is out)? Currently I am trying to do sum(jsonb_extract_path(data::jsonb, 'ui','hovers')->0->'when') to get the sum but cant figure how to recursively look into the json file and filter for mention.

Where exactly is the need for recursion?

pishpish
– pishpish

2018-05-01 18:35:05 +00:00
Commented May 1, 2018 at 18:35 — pishpish
– pishpish, Commented May 1, 2018 at 18:35

klin · Accepted Answer · 2018-05-01 19:33:15Z

2

Use json_array_elements() to extract columns from the json array:

select value->>'element' as element, value->>'time' as time_spent
from my_table
cross join json_array_elements(json_column->'Ui')

 element | time_spent 
-----+------------
 TG1     | 123
 TG1     | 125
 TG2     | 251
 TG2     | 259
(4 rows)

Modify the above query to get sums in groups by element:

select element, sum(time) as time_spent
from my_table
cross join lateral (
  select
    value->>'element' as element,
    case value->>'mention' when 'in' then -(value->>'time')::numeric else (value->>'time')::numeric end as time
  from json_array_elements(json_column->'Ui')) as elements
group by 1
order by 1

 element | time_spent 
---------+------------
 TG1     |          2
 TG2     |          8
(2 rows)

DbFiddle.

edited May 1, 2018 at 19:33

answered May 1, 2018 at 18:42

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum values extracted from a json array based on condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related