By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE cards (
card_id int,
phase_history varchar(500),
firstTimeIn varchar(500)
)
INSERT INTO cards (card_id,phase_history, firstTimeIn)
VALUES
('8837','[Start, Compliance, Contact, Down]', '[2022-08-11T13:44:24+00:00, 2022-08-11T13:44:25+00:00, 2022-08-25T17:37:19+00:00, 2022-08-11T13:44:26+00:00]'),
('6596','[Start, Compliance, Contact, Down]', '[2022-03-11T13:44:24+00:00, 2022-04-11T13:44:25+00:00, 2022-04-25T17:37:19+00:00, 2022-04-11T13:44:26+00:00]'),
('2416','[Start, Contact, Up]', '[2022-08-02T02:22:31+00:00, 2022-08-02T02:22:31+00:00, 2022-08-02T12:13:32+00:00]'),
('5424','[Start, Compliance, Contact, Lead, Down]', '[2022-09-01T12:51:24+00:00, 2022-09-01T12:51:25+00:00, 2022-09-01T13:25:52+00:00, 2022-09-01T12:51:26+00:00, 2022-09-01T16:47:31+00:00]')
4 rows affected
SELECT * FROM cards
| card_id | phase_history | firstTimeIn |
|---|---|---|
| 8837 | [Start, Compliance, Contact, Down] | [2022-08-11T13:44:24+00:00, 2022-08-11T13:44:25+00:00, 2022-08-25T17:37:19+00:00, 2022-08-11T13:44:26+00:00] |
| 6596 | [Start, Compliance, Contact, Down] | [2022-03-11T13:44:24+00:00, 2022-04-11T13:44:25+00:00, 2022-04-25T17:37:19+00:00, 2022-04-11T13:44:26+00:00] |
| 2416 | [Start, Contact, Up] | [2022-08-02T02:22:31+00:00, 2022-08-02T02:22:31+00:00, 2022-08-02T12:13:32+00:00] |
| 5424 | [Start, Compliance, Contact, Lead, Down] | [2022-09-01T12:51:24+00:00, 2022-09-01T12:51:25+00:00, 2022-09-01T13:25:52+00:00, 2022-09-01T12:51:26+00:00, 2022-09-01T16:47:31+00:00] |
SELECT d.card_id, a.phase_history, a.firstTimeIn
FROM cards d
CROSS APPLY (
SELECT
TRIM('[ ]' FROM c.[value]) AS phase_history,
TRIM('[ ]' FROM s.[value]) AS firstTimeIn
FROM OPENJSON(CONCAT('["', REPLACE(d.phase_history, ',', '","'), '"]')) c
LEFT OUTER JOIN OPENJSON(CONCAT('["', REPLACE(d.firstTimeIn, ',', '","'), '"]')) s
ON c.[key] = s.[key]
) a
WHERE a.phase_history IN ('Start', 'Compliance')
AND card_id = 8837
| card_id | phase_history | firstTimeIn |
|---|---|---|
| 8837 | Start | 2022-08-11T13:44:24+00:00 |
| 8837 | Compliance | 2022-08-11T13:44:25+00:00 |