Postgres - how select jsonb key value pairs as colums?

Question

having records in test table like so (metrics column is jsonb type):

id     name      metrics
1      machine1  {"metric1": 50, "metric2": 100}
2      machine2  {"metric1": 31, "metric2": 46}

I would like to select the metrics as additional columns, e.g. (pseudo-code):

Select *, json_each(test.metrics) from test;

to get the result like:

id  name       metric1   metric2 
1   machine1   50        100
2   machine2   31        46

Is this even possible?

Answer 1

Use the ->> operator:

select id, name, 
       metrics ->> 'metric1' as metric1,
       metrics ->> 'metric2' as metric2
from test;

Answer 2

You can simply use ->>

demo:db<>fiddle

SELECT
    id,
    name,
    metrics ->> 'metric1' as metric1,
    metrics ->> 'metric2' as metric2
FROM t

Note, that now the metric columns are of type text. If you want to them to be of type integer, you need to cast them additionally:

(metrics ->> 'metric1')::int