'Update date format in jsonb object in postgreSQL

Postgres table have one column as JSONB with below format

{
  "steps": [
    {
      "step": "Building",
      "status": "Complete",
      "end_date": "03/08/2018",
      "start_date": "03/08/2018"
    },
    {
      "step": "Underground Mechanical",
      "status": "Not Applicable",
      "end_date": "04/25/2018",
      "start_date": ""
    },
    {
      "step": "Close Mechanical Permit",
      "status": "Complete",
      "end_date": "04/25/2018",
      "start_date": ""
    }
  ],
  "people": [
    {
      "name": "Energy Resource Center",
      "role": "Contractor",
      "phone": "(000) 444-4447"
    },
    {
      "name": "XXX YYY",
      "role": "Owner",
      "phone": ""
    }
  ],
  "status": "Closed",
  "address": "XXX str",
  "sub_type": "Residential",
  "issue_date": "03/08/2018",
  "description": ""
}

steps can have n number of objects. I need to update end_date , start_date and issue_date to epoch date format. Please anyone can help with script.

sqlpostgresqljsonbepochpostgresql-13


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions