Postgresql Jsonb Field Sum

Question

I want to sum Unique ids in grouped result.

My data in database field:

user_ids:
{"user_id": [213, 199, 2]}
{"user_id": [213, 2]}
{"user_id": [212]}
{"user_id": [2, 213]}
{"user_id": [266, 199]}

My Query:

select 
ARRAY_AGG(DISTINCT CONCAT(jsonb_array_length(user_login_conflicts.user_ids -> 'user_id') - 1)) as user_ids
from "user_login_conflicts"
left join "users" on user_login_conflicts.user_ids -> 'user_id' @> CAST(CONCAT('[', users.id, ']') as JSONB)
where user_login_conflicts.user_ids -> 'user_id' is not null
group by "users"."id"

Result is below

user_ids:
{1,2}
{1,2}
{0}
{1,2}
{1}

I dont want to json result. I want sum result. Like this.

user_ids:
3
3
0
3
1

How can I sum my jsonb array result?

Edit the question and include the CREATE statements for both tables and INSERT statements for the sample data for them. — sticky bit
– sticky bit, Commented Jul 18, 2020 at 21:25
Yes @Bergi . If I can use sum, my problem will be solved. If I use this style: ARRAY_AGG(DISTINCT SUM(jsonb_array_length(user_login_conflicts.user_ids -> 'user_id') -1)) I am getting this error: ERROR: aggregate function calls cannot be nested. If I use this: SUM(DISTINCT CONCAT(jsonb_array_length(user_login_conflicts.user_ids -> 'user_id') -1)) Result: ERROR: function sum(text) does not exist — omero
– omero, Commented Jul 19, 2020 at 9:30
You should use the second one, and drop the CONCAT call that converts the integer to a string for no good reason. — Bergi
– Bergi, Commented Jul 19, 2020 at 11:13

Alex Funk · Accepted Answer · 2020-07-19 07:53:35Z

1

One way would be to use unnest and a subquery, similar to this:

select (select sum(s) from unnest(col1) s) from 
    (VALUES (array[1,2]), 
            (array[1,2]),
            (array[0]),
            (array[1,2]),
            (array[1])
    ) as q(col1);

https://www.db-fiddle.com/f/dtEv5BFAYJTvm45FtNjomL/0

answered Jul 19, 2020 at 7:53

Alex Funk

3562 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

Postgresql Jsonb Field Sum

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