2

I have a table with a json field attributes and a virtual tinyint (boolean) column flights with the following configuration:

JSON_UNQUOTE(attributes->"$.flights")

The flights property in the json field is a "real" boolean, eg. true. When adding this property to the json field MySQL returns Incorrect integer value: 'true' for column 'flights' at row 1 which is understandable.

My question is if it's possible to change the configuration for the virtual column so that it converts the boolean to the correct integer value on the fly?

1
  • Show us a sample of the JSON with a boolean in it. We need to see whether the "true" is quoted or not. Also, what process created the JSON?
    – Rick James
    Commented Jul 13, 2022 at 4:01

2 Answers 2

1

Eventually I found the solution myself after struggling through it. If anyone is looking for a similar answer - this is how I do it:

CAST(attributes->"$.flights" as signed int)

0
WITH cte AS (
    SELECT CAST('{"flights":true}' AS JSON) attributes
    UNION ALL
    SELECT '{"flights":false}'
)
SELECT JSON_PRETTY(attributes), 
       attributes->'$.flights' + 0
FROM cte
JSON_PRETTY(attributes) attributes->'$.flights' + 0
{"flights": true} 1
{"flights": false} 0

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.