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);
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