sqlpostgresqlpostgres-10

Postgres - how select jsonb key value pairs as colums?

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?

Solution

  • Use the ->> operator:

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