I'm creating an extension for osTicket. I need to know the history of ticket statuses over time. For this reason, I read the status from the ost_thread_event.data column from the database. The date column is not always JSON with status key. Sometimes it is NULL or JSON without status key.
SELECT id, data, timestamp FROM ost_thread_event;
| id | data | timestamp |
|---|---|---|
| 1 | NULL |
2021-09-15 09:28:53 |
| 2 | {"status":6} |
2022-03-16 11:52:06 |
| 3 | {"status":[2,"Completed"]} |
2022-03-17 10:27:50 |
| 4 | {"team":1} |
2022-12-07 13:40:27 |
I want to create a JSONPath that will return me only a status digit.
For {"status":6} it is $.status
For {"status":[2,"Completed"]} it is $.status[0]
Unfortunately, I don't know how to combine these two queries. If the element is an array take the first element from the array, otherwise take the element. How can I combine it?
