How do I write the SQL query to fetch the data (image) from JSON

Question

Json Object

{"images": ["https://bijnis.s3.ap-south-1.amazonaws.com/2487dc60-c3b5-4cf9-a3a2-34f73683ac5a.jpg"], 
"remarks": "done", 
"documentList": [{"id": "GST", "value": "GST"}]}, GST, https://bijnis.s3.ap-south-1.amazonaws.com/2487dc60-c3b5-4cf9-a3a2-34f73683ac5a.jpg

select
    json_extract(my_json_field, '$.images'),
    json_extract(my_json_field, '$.remarks'),
    json_extract(my_json_field, '$.documentList.id'),
    json_extract(my_json_field, '$.documentList.value')
from Jason Object;

Answer 1

Use JSON_VALUE as follows :

WITH cte as (
select '{"images": ["https://bijnis.s3.ap-south-1.amazonaws.com/2487dc60-c3b5-4cf9-a3a2-34f73683ac5a.jpg"], 
"remarks": "done", 
"documentList": [{"id": "GST", "value": "GST"}]}' as json
)
select JSON_VALUE(json, '$.images') AS images,
    JSON_VALUE(json, '$.remarks') AS remarks,
    JSON_VALUE(json, '$.documentList[0].id') AS id,
    JSON_VALUE(json, '$.documentList[0].value') AS value
from cte;