Is it possible to fetch and compare an element which resides in a nested Json string format column in database? Via SQL Query

I have ‘a unique-id’. I want to fetch records from table on basis of that unique-id. I have a column named “request body” that contains a nested json string which is of type text. Is there any way i can compare ‘unique-id’ with the ‘unique-id’ inside the json string cloumn i-e request body?

Answer

Apologies, I am new to stackoverflow. For anyone looking for the solution, below are the two approaches:

APPROACH 1

SELECT t.request_body
FROM table t
WHERE cast(request_body as JSON) ->> 'uniqueId' ='123'

APPROACH 2

SELECT t.request_body
FROM table t
WHERE (substr(t.request_body, position ('uniqueId' in request_body)+11,3) ='123'

Note: 11 represents lenght of ‘uniqueId”:”‘ and 3 for the following id 123