Parsing a string of 'single quotes' as json in presto

Question

~I want to cast a string of 'single quotes' string like below code.~

---

Sorry, I changed the question. i replace the 'single quotes' to "double quotes"

Example

WITH data as (
    SELECT 
        '{"svod": True, "awards": [], "copyright": "© ROI VISUAL / EBS All rights Reserved.", "providerId": "50176b59-9bae-40ed-bedd-167786953c73", "releaseYear": 2018, "rating_levels": {"drugs": "none", "theme": "none", "horror": "none", "language": "none", "violence": "none", "sex_and_nudity": "none", "imitable_behavior": "none"}, "display_runtime": "00:05:00", "production_company": "ROIVISUAL"}' as meta
)

SELECT 
    -- json_parse(meta) -- X (Error : Cannot convert value to JSON)
    cast(meta as json) as meta_json, -- O > "{\"svod\": True, \"awards\": [], \"copyright\": \"© ROI VISUAL / EBS All rights Reserved.\", \"providerId\": \"50176b59-9bae-40ed-bedd-167786953c73\", \"releaseYear\": 2018, \"rating_levels\": {\"drugs\": \"none\", \"theme\": \"none\", \"horror\": \"none\", \"language\": \"none\", \"violence\": \"none\", \"sex_and_nudity\": \"none\", \"imitable_behavior\": \"none\"}, \"display_runtime\": \"00:05:00\", \"production_company\": \"ROIVISUAL\"}"
    json_extract_scalar(cast(meta as json), '$.providerId') as meta_providerId -- Empty Return
FROM data

Expected Results

SELECT 
 json_extract_scalar(cast(meta as json), '$.releaseYear'), -- 2018
 json_extract_scalar(cast(meta as json), '$.providerId') -- "50176b59-9bae-40ed-bedd-167786953c73"
FROM data

I've referred to this before, but it didn't work out.

• How to cast varchar to MAP(VARCHAR,VARCHAR) in presto
• How to extract keys in a nested json array object in Presto?
• Presto Build JSON Array with Different Data Types

Do you have any advice for me?

Thanks!

Answer 1

Your JSON appears to be invalid.

Use either "True" (with quotes) or true (without quotes, lowercase) to make your JSON valid.