Sum up a value in Json row inside PostgreSQL

Question

Suppose I have a table in PostgreSQL that has two column, Id and Doc. In the Doc column a Json object like below:

{"eid":{"a":5, "b":6, "c":9}, "time":12345}
{"eid":{"b":6, "c":9, "x":25}, "time":13255}

I would be appreciate to help me to write a query which sums up 'a' eids according to specified time. Thanks

The nearest thing that i could do is: select json_each(events.doc->'eid') from events that produced records like ('a',5). — Ehsan Toghian
– Ehsan Toghian, Commented Jul 12, 2015 at 9:47

Marth · Accepted Answer · 2015-07-12 10:19:27Z

3

I'm unsure what you mean by "according to specified time", but I think you want :

SELECT * FROM j;
┌───────────────────────────────────────────────────┐
│                        doc                        │
├───────────────────────────────────────────────────┤
│ {"eid": {"a": 5, "b": 6, "c": 9}, "time": 12345}  │
│ {"eid": {"b": 6, "c": 9, "x": 25}, "time": 13255} │
└───────────────────────────────────────────────────┘
(2 rows)

SELECT SUM((doc#>>'{eid,a}')::integer) 
FROM j 
WHERE (doc->>'time')::integer = 12345;
┌─────┐
│ sum │
├─────┤
│   5 │
└─────┘
(1 row)

EDIT

To get all the keys at once (you might need to change jsonb_* to json_* depending on your schema) :

SELECT key, SUM(jsonb_extract_path_text(doc, 'eid', key)::integer)
FROM j, jsonb_object_keys(doc->'eid') sub(key)
GROUP BY key
;
┌─────┬─────┐
│ key │ sum │
├─────┼─────┤
│ x   │  25 │
│ b   │  12 │
│ a   │   5 │
│ c   │  18 │
└─────┴─────┘
(4 rows)

edited Jul 12, 2015 at 10:19

answered Jul 12, 2015 at 9:54

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

3 Comments

Ehsan Toghian Over a year ago

Thanks a lot @Marth, if i want to have a result that each eid is sumed in a row (like <'a',5>, <'b',12>, <'c', 18> and <'x', 25> ), what should I do?

Marth Over a year ago

@ehsantoghian : edited my answer. However I'm unsure of how you intend to use the time value in this query, since it seems you want to take all eids at once.

Ehsan Toghian Over a year ago

Thanks @Marth, the time is used when i want to confine the output events (eid) to a period of time. For example i want to see the event between 9 AM and 5 PM.

klin · Accepted Answer · 2015-07-12 10:39:08Z

1

To sum all values of array eid for a given time use this query:

select time, sum(value)
from (
    select
        (json_each(doc->'eid')).value::text::int,
        doc->>'time' as time
    from events 
    ) alias
group by 1
order by 1

answered Jul 12, 2015 at 10:39

klin

123k15 gold badges240 silver badges262 bronze badges

Collectives™ on Stack Overflow

Sum up a value in Json row inside PostgreSQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related