I have a JSONB column called metrics in a table events. It stores various metrics as a flat hash, e.g.
{"m1": 123, "m2": 122.3, "m3": 32}
I would like to extract all the values stored in that column. Is it possible? I have found a function jsonb_object_keys(jsonb), but I failed to find anything similar for values.
Use jsonb_each() for this purpose:
WITH json_test(data) AS ( VALUES
('{"m1": 123, "m2": 122.3, "m3": 32}'::JSONB)
)
SELECT element.value
FROM json_test jt, jsonb_each(jt.data) as element;
Output:
value
-------
123
122.3
32
(3 rows)
Use jsonb_each() in a lateral join:
with val as (
select '{"m1": 123, "m2": 122.3, "m3": 32}'::jsonb js
)
select key, value
from val,
lateral jsonb_each(js);
key | value
-----+-------
m1 | 123
m2 | 122.3
m3 | 32
(3 rows)
Using json_each you can extract the values with:
SELECT value FROM json_each('{"m1": 123, "m2": 122.3, "m3": 32}')
Output
value
-----
123
122.3
32
