Matt K Matt K - 2 months ago 10
SQL Question

How do I write SQL to Substring data in this table?

Here's the data in 1 field in the database:

{"image": null, "endDate": "2016-08-26",
"features": {"Attendee List": true, "Event Feedback": true, "Session Feedback": true},
"startDate": "2016-08-25",
"description": null, "selectedTimeZone": "America/Chicago", "popularityThreshold": 45, "twentyFourHourClockOn": false}


I need to extract the values for "endDate" and the "startDate" something like this:

Select t.somefield, t.startdate(substring()), t.enddate(substring)) from tablename


Something like this:

value, 2016-08-25, 2016-08-26

The database is PostgreSQL.
here's a sample select query

Need some assistance writing the substring (it's been a while), and I'm only writing the query for reporting needs.

Answer

Use Postgres' JSON functions:

select (content::json ->> 'startDate')::date as start_date,
       (content::json ->> 'endDate')::date as end_date, 
       e.some_column
from events e
Comments