Суммарные значения, извлеченные из массива json на основе условия

У меня есть поле JSON в таблице, как показано ниже

{
"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
    }
]
 }

Я намерен получить что-то вроде ниже

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

но оказались совершенно безуспешными. Как я могу получить разницу (сумма за все время, когда упоминание включено - сумма за все время, когда упоминание отсутствует)? В настоящее время я пытаюсь сделать sum(jsonb_extract_path(data::jsonb, 'ui','hovers')->0->'when'), чтобы получить сумму, но не могу понять, как рекурсивно просматривать файл json и фильтровать его для упоминания.

Где именно необходимость в рекурсии?

— 01.05.2018 20:35

json postgresql aggregate-functions

01.05.2018 20:27

Как сделать HTTP-запрос в Javascript?

В JavaScript вы можете сделать HTTP-запрос, используя объект XMLHttpRequest или более новый API fetch. Вот пример для обоих методов:

570

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Используйте json_array_elements() для извлечения столбцов из массива json:

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)

Измените приведенный выше запрос, чтобы получить суммы в группах по элементам:

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.

01.05.2018 20:42