0

~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.

Do you have any advice for me?

Thanks!

이인영
  • 3
  • 3
  • Did you try to use the `json_extract`-function in your insert? https://prestodb.io/docs/current/functions/json.html?highlight=json#json-functions – Natrium Aug 24 '22 at 05:20
  • @Natrium Yeah, but It doesn't convert to json fisrt. – 이인영 Aug 24 '22 at 05:24
  • It does not convert TO json, it converts FROM json. That's how I understood your question: I have a json and I want to insert values from that json into my database. – Natrium Aug 24 '22 at 05:59
  • @Natrium Sorry for confusing. I changed the Question. – 이인영 Aug 24 '22 at 06:27
  • Could you try it without casting to json? According to the docs, it should be able to work a string/varchar without casting. – Natrium Aug 24 '22 at 06:46
  • I tried many times without casting to json. but, it not working. – 이인영 Aug 24 '22 at 06:48

1 Answers1

0

Your JSON appears to be invalid.

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

Natrium
  • 30,772
  • 17
  • 59
  • 73