Comparing dates within a text colum, formated as JSON, in PostgreSQL-CodePudding

I've got a bizarre setup I am working with where, I have a table with multiple columns. One of the columns, I'll call it "weird-column" has data type of text but the information that is stored within that column appears to be listed as JSON.

"Why wasn't this column data type set as JSON or JSONB?" No idea - this is a DB that I was given to work with.

Example:

{
  "id": "numbers-and-letters-go-here":[
    {
      "Code": "thing"
    }
  ],
  "issuedt": "03/18/1989",
  "expirationdate": "03/18/3089",
  "firstname": "Funky",
  "middlename": "Cold",
  "lastname": "Medina"
}

Now, I need to compare dates stored within weird-column, to see if any expriationdate expire before issuedt. I need to do this for every entry in weird-column and see output for the entries for each record which expired before it was even issued.

My initial plan was to use something like

select * from table_1
where issuedt < expriationdate

but since all of this is data stored within weird-column, that wouldn't work. Is there a way to specify this? Something like

select * from table_1
where weird-column.issuedt < weird-column.expriationdate

I don't have the knowledge to describe what it is that I need to do, but have a vague understanding of what I need done. Any suggestions at all would be extremley helpful.

CodePudding user response：

If your weird-column is a well formed json then you can simply cast it to jsonb

e.g

select '{"name":"xyz", "age" : 34}'::jsonb

once converted to json, you can use various operators including the -> operator to select a particular field in the json and further operate on it

select (('{"name":"xyz", "age" : 34}'::jsonb) -> 'age')::integer

do check out this page for more details https://www.postgresql.org/docs/9.5/functions-json.html

CodePudding user response：

demo
your text column is ill formed for converting it to jsonb/json.

"id": "numbers-and-letters-go-here":[
    {
      "Code": "thing"
    }
  ]

jsonb/json is more like "key":"value", it cannot "key":"value": "value1". I changed it to

  "id": "numbers-and-letters-go-here",
  "id1": [
    {
      "Code": "thing"
    }
  ]

final code for compare issuedt with expirationdate

SELECT
    to_date((weird::jsonb)->>'issuedt','MM/DD/YYYY')
FROM
    t1
where to_date((weird::jsonb)->>'issuedt','MM/DD/YYYY')
    <  to_date((weird::jsonb)->>'expirationdate','MM/DD/YYYY') ;