How to make a similar request if the field is text and not json?

Question

PostgreSQL

Is it possible to write the same request if the raw field is not jsonb, but text?

SELECT
tp."raw"->>'errorCode' AS error_code,
count(tp."raw"->>'errorCode') AS "count"
FROM transaction_payment tp
WHERE (tp.date_created BETWEEN '2022-03-21 00: 00:00' AND '2022-03-27 23:59:59')
AND tp."raw" IS NOT NULL
AND tp."source" = 'mts-bpc'
GROUP BY tp."raw"->>' errorCode'
ORDER BY "count" DESC;

There is a mistake in the architecture, the request is needed for one-time statistics, so now rewrite it to the extreme only, since it is necessary to analyze the logic associated with this data. Maybe one of the gurus will tell you an option?

Answer 1

SELECT ('{"data": "test", "count": 8}'::json->>'count')::int + 2

Answer 2

Is it possible to write the same request if the raw field is not jsonb, but text?

Well, fuck the problem - replace tp."raw"->>'errorCode' with tp."raw"::JSONB->>'errorCode'
By the way, this applies to both field types. Just for JSON - redundant.