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

user_7671906 0
Viewed 211

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);
jsonb aggregate casting postgresql
1 Answers

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

user_9398600
Related