1

In a Redshift database, given a field containing the following json:

{"0":true,"1":true,"2":true,"3":true,"4":true,"5":true,"6":true}

Although I can extract true using json_extract_path_text(my_field, '0'), when I'm trying to extract an element and cast it to a boolean as follow:

SELECT 
    json_extract_path_text(my_field, '0')::boolean
FROM my_table

I then get the following error:

cannot cast type text to boolean

I do not understand as the following is working without any issue:

SELECT 'true'::boolean, 'false'::boolean;
fpietka
  • 1,027
  • 1
  • 10
  • 23
  • What does `SELECT json_extract_path_text(my_field, '0') FROM my_table` return for that row? `true`? – nofinator Nov 21 '22 at 16:31
  • Yes. I don't know how to double check the type of it though, but according to the error it seems like a text – fpietka Nov 22 '22 at 12:23
  • 1
    Can you cast to text and then to boolean? Just a thought. My guess as to what is happening is that the value is sometimes not defined and that is "missing" value is not mapping to boolean. You may need to massage these "undefined" values to null with a decode() function. – Bill Weiner Nov 23 '22 at 11:36
  • 1
    I have a minimal example that reproduce the problem: `select json_extract_path_text('{"0":true}', '0')::boolean;`. If I cast to `text` before, I have a slightly different error: `cannot cast type character varying to boolean` – fpietka Nov 23 '22 at 13:23

1 Answers1

2

This post seems to answer the problem: https://stackoverflow.com/a/45799072

SELECT BOOLIN(TEXTOUT(json_extract_path_text('{"0":true}', '0')));
labantoine
  • 36
  • 3