I have created a postgres specific sql with which I can succesfully search a json string value for a the label element. The json would look like this and is stored as a string in the db:

{"list":[{"label":"Label", "taskReference":"myReference",...}]}

The query I have created:

SELECT ELEM ->> 'label' 
FROM MY_TABLE
CROSS JOIN LATERAL jsonb_array_elements(DATA::JSONB -> 'list') AS ELEM 
WHERE ELEM ->> 'taskReference' = 'myReference';

the result would be

Label

This is however postgres specific. The problem is that we don’t always use the same database type depending on environment. Is there anyway to achieve the above with “standard” generic sql functions?