PostgreSQL: Json function-CodePudding

I have a postgreSQL column that looks like this:

{
  "table": false,
  "time": {
    "user": {
      "type": "admin"
    },
    "end": {
      "Always": null
    },
    "sent": {
      "Never": 1356
    },
    "increments": 5,
    "increment_type": "weeks",
    "type": "days"
  }
}

I would like to extract from the json file "Increments = 5 and Increment_type= weeks). result would be -- Column_a = 5 weeks

CodePudding user response：

Use the dereferencing operators, -> and ->> to get what you need:

select concat(
         colname->'time'->>'increments',
         ' ',
         colname->'time'->>'increment_type'
       ) as column_a
  from tablename;