Aggregate numeric values from jsonb array of records (including NULL values)

Question

I'm using postgres 9.5 and my data is integers like this :

id | v1 | v2 | v3
---+----+----+------
 1 | 10 | 3  | null     
 2 | 5  |null| 1
 3 |null| 2  | 7

I have created a jsonb array like this:

 [{"v1": 10, "v2": 3, "v3": null}]

I'd like to run comparisons and Aggregations (IE sum all v1).

1) is jsonb_array_elements the correct operation or is there an easier way?

2) if jsonb_array_elements is best way, how do I cast to integer and generate null value so I can run comparisons/aggs?

See DBFIDDLE

I've looked at several stack questions and this is as far as I have gotten:

SELECT id, x->'v1' AS v1
FROM   base,jsonb_array_elements(j) t(x);

edited, thanks again. You're answers are always so spot on. If you ever make an on line course on postgres, I'd be the first to signup. — moonshot
– moonshot, Commented Dec 28, 2018 at 16:20

Erwin Brandstetter · Accepted Answer · 2018-12-28 21:00:13Z

2

You were almost there.

SELECT sum((x->>'v1')::int) AS v1_sum
FROM   base, jsonb_array_elements(j) t(x);

Use the ->> operator instead of -> to get text values; then cast. Casting a jsonb NULL to integer fails, and you want text to begin with.

If you have more value columns, the - operator might also become attractive: form a jsonb record from the whole row and subtract (delete) the id key:

SELECT id, json_agg(j1) AS j
FROM  (SELECT id, to_jsonb(t) - 'id' AS j1 FROM t) t1
GROUP  BY id;

db<>fiddle here

edited Dec 28, 2018 at 21:00

answered Dec 28, 2018 at 4:48

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Aggregate numeric values from jsonb array of records (including NULL values)

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