Extract values from Postgres JSONB column

Question

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.

Answer 1

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)

Answer 2

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)    

Answer 3

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