Formatting true/false in sql












0















I am passing a dict that I get from mysql directly into json. Unfortunately, the json fails when trying to parse the boolean with the following error:




'Current token (VALUE_NUMBER_INT) not of boolean type




Is the following the preferred way to cast a TINYINT(1) to a javascript boolean?



if(tm.is_live, 'true', 'false') live_for_preorder,





mysql sql json







share|improve this question

























  • What will be the possible values in tm.is_live field ?

    – Madhur Bhaiya
    Nov 15 '18 at 4:46











  • This does not look like MySQL error. But it is looking as errror from Elastic Search: github.com/elastic/elasticsearch/issues/10056

    – Madhur Bhaiya
    Nov 15 '18 at 4:47











  • How did you get the dict? What MySQL version? What table structure/query?

    – danblack
    Nov 15 '18 at 4:50











  • @MadhurBhaiya the tm.is_live field is a TINYINT(1) and the values will be either 0 or 1. I think json (ES) is excepting 'true'/'false'.

    – David L
    Nov 15 '18 at 4:52






  • 1





    @DavidL your existing query should work. You can also convert If() to ANSI SQL compliant CASE..WHEN (if you change DB later, as IF() is MySQL specific only). In case of CASE..WHEN, it would look as follows: CASE tm.is_live WHEN 0 THEN 'false' ELSE 'true' END AS live_for_preorder

    – Madhur Bhaiya
    Nov 15 '18 at 5:21
















0















I am passing a dict that I get from mysql directly into json. Unfortunately, the json fails when trying to parse the boolean with the following error:




'Current token (VALUE_NUMBER_INT) not of boolean type




Is the following the preferred way to cast a TINYINT(1) to a javascript boolean?



if(tm.is_live, 'true', 'false') live_for_preorder,





mysql sql json







share|improve this question

























  • What will be the possible values in tm.is_live field ?

    – Madhur Bhaiya
    Nov 15 '18 at 4:46











  • This does not look like MySQL error. But it is looking as errror from Elastic Search: github.com/elastic/elasticsearch/issues/10056

    – Madhur Bhaiya
    Nov 15 '18 at 4:47











  • How did you get the dict? What MySQL version? What table structure/query?

    – danblack
    Nov 15 '18 at 4:50











  • @MadhurBhaiya the tm.is_live field is a TINYINT(1) and the values will be either 0 or 1. I think json (ES) is excepting 'true'/'false'.

    – David L
    Nov 15 '18 at 4:52






  • 1





    @DavidL your existing query should work. You can also convert If() to ANSI SQL compliant CASE..WHEN (if you change DB later, as IF() is MySQL specific only). In case of CASE..WHEN, it would look as follows: CASE tm.is_live WHEN 0 THEN 'false' ELSE 'true' END AS live_for_preorder

    – Madhur Bhaiya
    Nov 15 '18 at 5:21














0












0








0








I am passing a dict that I get from mysql directly into json. Unfortunately, the json fails when trying to parse the boolean with the following error:




'Current token (VALUE_NUMBER_INT) not of boolean type




Is the following the preferred way to cast a TINYINT(1) to a javascript boolean?



if(tm.is_live, 'true', 'false') live_for_preorder,





mysql sql json







share|improve this question
















I am passing a dict that I get from mysql directly into json. Unfortunately, the json fails when trying to parse the boolean with the following error:




'Current token (VALUE_NUMBER_INT) not of boolean type




Is the following the preferred way to cast a TINYINT(1) to a javascript boolean?



if(tm.is_live, 'true', 'false') live_for_preorder,





mysql sql json




mysql sql json






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 4:53







David L

















asked Nov 15 '18 at 4:43









David LDavid L

11617




11617













  • What will be the possible values in tm.is_live field ?

    – Madhur Bhaiya
    Nov 15 '18 at 4:46











  • This does not look like MySQL error. But it is looking as errror from Elastic Search: github.com/elastic/elasticsearch/issues/10056

    – Madhur Bhaiya
    Nov 15 '18 at 4:47











  • How did you get the dict? What MySQL version? What table structure/query?

    – danblack
    Nov 15 '18 at 4:50











  • @MadhurBhaiya the tm.is_live field is a TINYINT(1) and the values will be either 0 or 1. I think json (ES) is excepting 'true'/'false'.

    – David L
    Nov 15 '18 at 4:52






  • 1





    @DavidL your existing query should work. You can also convert If() to ANSI SQL compliant CASE..WHEN (if you change DB later, as IF() is MySQL specific only). In case of CASE..WHEN, it would look as follows: CASE tm.is_live WHEN 0 THEN 'false' ELSE 'true' END AS live_for_preorder

    – Madhur Bhaiya
    Nov 15 '18 at 5:21



















  • What will be the possible values in tm.is_live field ?

    – Madhur Bhaiya
    Nov 15 '18 at 4:46











  • This does not look like MySQL error. But it is looking as errror from Elastic Search: github.com/elastic/elasticsearch/issues/10056

    – Madhur Bhaiya
    Nov 15 '18 at 4:47











  • How did you get the dict? What MySQL version? What table structure/query?

    – danblack
    Nov 15 '18 at 4:50











  • @MadhurBhaiya the tm.is_live field is a TINYINT(1) and the values will be either 0 or 1. I think json (ES) is excepting 'true'/'false'.

    – David L
    Nov 15 '18 at 4:52






  • 1





    @DavidL your existing query should work. You can also convert If() to ANSI SQL compliant CASE..WHEN (if you change DB later, as IF() is MySQL specific only). In case of CASE..WHEN, it would look as follows: CASE tm.is_live WHEN 0 THEN 'false' ELSE 'true' END AS live_for_preorder

    – Madhur Bhaiya
    Nov 15 '18 at 5:21

















What will be the possible values in tm.is_live field ?

– Madhur Bhaiya
Nov 15 '18 at 4:46





What will be the possible values in tm.is_live field ?

– Madhur Bhaiya
Nov 15 '18 at 4:46













This does not look like MySQL error. But it is looking as errror from Elastic Search: github.com/elastic/elasticsearch/issues/10056

– Madhur Bhaiya
Nov 15 '18 at 4:47





This does not look like MySQL error. But it is looking as errror from Elastic Search: github.com/elastic/elasticsearch/issues/10056

– Madhur Bhaiya
Nov 15 '18 at 4:47













How did you get the dict? What MySQL version? What table structure/query?

– danblack
Nov 15 '18 at 4:50





How did you get the dict? What MySQL version? What table structure/query?

– danblack
Nov 15 '18 at 4:50













@MadhurBhaiya the tm.is_live field is a TINYINT(1) and the values will be either 0 or 1. I think json (ES) is excepting 'true'/'false'.

– David L
Nov 15 '18 at 4:52





@MadhurBhaiya the tm.is_live field is a TINYINT(1) and the values will be either 0 or 1. I think json (ES) is excepting 'true'/'false'.

– David L
Nov 15 '18 at 4:52




1




1





@DavidL your existing query should work. You can also convert If() to ANSI SQL compliant CASE..WHEN (if you change DB later, as IF() is MySQL specific only). In case of CASE..WHEN, it would look as follows: CASE tm.is_live WHEN 0 THEN 'false' ELSE 'true' END AS live_for_preorder

– Madhur Bhaiya
Nov 15 '18 at 5:21





@DavidL your existing query should work. You can also convert If() to ANSI SQL compliant CASE..WHEN (if you change DB later, as IF() is MySQL specific only). In case of CASE..WHEN, it would look as follows: CASE tm.is_live WHEN 0 THEN 'false' ELSE 'true' END AS live_for_preorder

– Madhur Bhaiya
Nov 15 '18 at 5:21












2 Answers
2






active

oldest

votes


















1














If you want to convert to a MySQL boolean, simply do:



select (tm.is_live <> 0) as live_for_preorder,


If this is accepted by Javascript, then this would be the simplest solution.






share|improve this answer































    1














    I seem to recall MySQL is one of those rare databases that lets you use IF in normal SELECT statements where most dbms would insist on case, IF being reserved for control flow



    As such it appears that MySQL is telling you you’re not providing IF with a Boolean when it wants one. Convert your statement to a comparison that returns a Boolean:



    if(tm.is_live=1, 'true', 'false') live_for_preorder,


    (If you’re using 1 for true and 0 for false)



    To make your db skill set more portable it would be good to get into the habit of using CASE rather than IF in select queries:



    CASE tm.is_live WHEN 1 THEN 'true' ELSE 'false' END live_for_preorder,
    

    
CASE WHEN tm.is_live = 1 THEN 'true' ELSE 'false' END live_for_preorder,


    A case can have any number of WHEN, but you can’t mix the forms, it’s either CASE variable WHEN comparevalue THEN outputvalue [when when when else] END Or it’s CASE WHEN test THEN output [when when when else] END






    share|improve this answer


























    • Sql server will allow you to use iif(condition, result if true, result if false) in a select statement but it is very rarely used (at least from what I have read/seen). docs.microsoft.com/en-us/sql/t-sql/functions/…

      – Dwight Reynoldson
      Nov 15 '18 at 7:33






    • 1





      That's implemented as a function though, right? And you can't use IIF for control flow? All major dbms I know of insist on CASE for assignment and IF for flow, except MySQL allows either for both (dirty!)

      – Caius Jard
      Nov 15 '18 at 7:38













    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53312550%2fformatting-true-false-in-sql%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    If you want to convert to a MySQL boolean, simply do:



    select (tm.is_live <> 0) as live_for_preorder,


    If this is accepted by Javascript, then this would be the simplest solution.






    share|improve this answer




























      1














      If you want to convert to a MySQL boolean, simply do:



      select (tm.is_live <> 0) as live_for_preorder,


      If this is accepted by Javascript, then this would be the simplest solution.






      share|improve this answer


























        1












        1








        1







        If you want to convert to a MySQL boolean, simply do:



        select (tm.is_live <> 0) as live_for_preorder,


        If this is accepted by Javascript, then this would be the simplest solution.






        share|improve this answer













        If you want to convert to a MySQL boolean, simply do:



        select (tm.is_live <> 0) as live_for_preorder,


        If this is accepted by Javascript, then this would be the simplest solution.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 15 '18 at 12:50









        Gordon LinoffGordon Linoff

        781k35310414




        781k35310414

























            1














            I seem to recall MySQL is one of those rare databases that lets you use IF in normal SELECT statements where most dbms would insist on case, IF being reserved for control flow



            As such it appears that MySQL is telling you you’re not providing IF with a Boolean when it wants one. Convert your statement to a comparison that returns a Boolean:



            if(tm.is_live=1, 'true', 'false') live_for_preorder,


            (If you’re using 1 for true and 0 for false)



            To make your db skill set more portable it would be good to get into the habit of using CASE rather than IF in select queries:



            CASE tm.is_live WHEN 1 THEN 'true' ELSE 'false' END live_for_preorder,
            

            
CASE WHEN tm.is_live = 1 THEN 'true' ELSE 'false' END live_for_preorder,


            A case can have any number of WHEN, but you can’t mix the forms, it’s either CASE variable WHEN comparevalue THEN outputvalue [when when when else] END Or it’s CASE WHEN test THEN output [when when when else] END






            share|improve this answer


























            • Sql server will allow you to use iif(condition, result if true, result if false) in a select statement but it is very rarely used (at least from what I have read/seen). docs.microsoft.com/en-us/sql/t-sql/functions/…

              – Dwight Reynoldson
              Nov 15 '18 at 7:33






            • 1





              That's implemented as a function though, right? And you can't use IIF for control flow? All major dbms I know of insist on CASE for assignment and IF for flow, except MySQL allows either for both (dirty!)

              – Caius Jard
              Nov 15 '18 at 7:38


















            1














            I seem to recall MySQL is one of those rare databases that lets you use IF in normal SELECT statements where most dbms would insist on case, IF being reserved for control flow



            As such it appears that MySQL is telling you you’re not providing IF with a Boolean when it wants one. Convert your statement to a comparison that returns a Boolean:



            if(tm.is_live=1, 'true', 'false') live_for_preorder,


            (If you’re using 1 for true and 0 for false)



            To make your db skill set more portable it would be good to get into the habit of using CASE rather than IF in select queries:



            CASE tm.is_live WHEN 1 THEN 'true' ELSE 'false' END live_for_preorder,
            

            
CASE WHEN tm.is_live = 1 THEN 'true' ELSE 'false' END live_for_preorder,


            A case can have any number of WHEN, but you can’t mix the forms, it’s either CASE variable WHEN comparevalue THEN outputvalue [when when when else] END Or it’s CASE WHEN test THEN output [when when when else] END






            share|improve this answer


























            • Sql server will allow you to use iif(condition, result if true, result if false) in a select statement but it is very rarely used (at least from what I have read/seen). docs.microsoft.com/en-us/sql/t-sql/functions/…

              – Dwight Reynoldson
              Nov 15 '18 at 7:33






            • 1





              That's implemented as a function though, right? And you can't use IIF for control flow? All major dbms I know of insist on CASE for assignment and IF for flow, except MySQL allows either for both (dirty!)

              – Caius Jard
              Nov 15 '18 at 7:38
















            1












            1








            1







            I seem to recall MySQL is one of those rare databases that lets you use IF in normal SELECT statements where most dbms would insist on case, IF being reserved for control flow



            As such it appears that MySQL is telling you you’re not providing IF with a Boolean when it wants one. Convert your statement to a comparison that returns a Boolean:



            if(tm.is_live=1, 'true', 'false') live_for_preorder,


            (If you’re using 1 for true and 0 for false)



            To make your db skill set more portable it would be good to get into the habit of using CASE rather than IF in select queries:



            CASE tm.is_live WHEN 1 THEN 'true' ELSE 'false' END live_for_preorder,
            

            
CASE WHEN tm.is_live = 1 THEN 'true' ELSE 'false' END live_for_preorder,


            A case can have any number of WHEN, but you can’t mix the forms, it’s either CASE variable WHEN comparevalue THEN outputvalue [when when when else] END Or it’s CASE WHEN test THEN output [when when when else] END






            share|improve this answer















            I seem to recall MySQL is one of those rare databases that lets you use IF in normal SELECT statements where most dbms would insist on case, IF being reserved for control flow



            As such it appears that MySQL is telling you you’re not providing IF with a Boolean when it wants one. Convert your statement to a comparison that returns a Boolean:



            if(tm.is_live=1, 'true', 'false') live_for_preorder,


            (If you’re using 1 for true and 0 for false)



            To make your db skill set more portable it would be good to get into the habit of using CASE rather than IF in select queries:



            CASE tm.is_live WHEN 1 THEN 'true' ELSE 'false' END live_for_preorder,
            

            
CASE WHEN tm.is_live = 1 THEN 'true' ELSE 'false' END live_for_preorder,


            A case can have any number of WHEN, but you can’t mix the forms, it’s either CASE variable WHEN comparevalue THEN outputvalue [when when when else] END Or it’s CASE WHEN test THEN output [when when when else] END







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 15 '18 at 7:35

























            answered Nov 15 '18 at 7:24









            Caius JardCaius Jard

            12k21240




            12k21240













            • Sql server will allow you to use iif(condition, result if true, result if false) in a select statement but it is very rarely used (at least from what I have read/seen). docs.microsoft.com/en-us/sql/t-sql/functions/…

              – Dwight Reynoldson
              Nov 15 '18 at 7:33






            • 1





              That's implemented as a function though, right? And you can't use IIF for control flow? All major dbms I know of insist on CASE for assignment and IF for flow, except MySQL allows either for both (dirty!)

              – Caius Jard
              Nov 15 '18 at 7:38





















            • Sql server will allow you to use iif(condition, result if true, result if false) in a select statement but it is very rarely used (at least from what I have read/seen). docs.microsoft.com/en-us/sql/t-sql/functions/…

              – Dwight Reynoldson
              Nov 15 '18 at 7:33






            • 1





              That's implemented as a function though, right? And you can't use IIF for control flow? All major dbms I know of insist on CASE for assignment and IF for flow, except MySQL allows either for both (dirty!)

              – Caius Jard
              Nov 15 '18 at 7:38



















            Sql server will allow you to use iif(condition, result if true, result if false) in a select statement but it is very rarely used (at least from what I have read/seen). docs.microsoft.com/en-us/sql/t-sql/functions/…

            – Dwight Reynoldson
            Nov 15 '18 at 7:33





            Sql server will allow you to use iif(condition, result if true, result if false) in a select statement but it is very rarely used (at least from what I have read/seen). docs.microsoft.com/en-us/sql/t-sql/functions/…

            – Dwight Reynoldson
            Nov 15 '18 at 7:33




            1




            1





            That's implemented as a function though, right? And you can't use IIF for control flow? All major dbms I know of insist on CASE for assignment and IF for flow, except MySQL allows either for both (dirty!)

            – Caius Jard
            Nov 15 '18 at 7:38







            That's implemented as a function though, right? And you can't use IIF for control flow? All major dbms I know of insist on CASE for assignment and IF for flow, except MySQL allows either for both (dirty!)

            – Caius Jard
            Nov 15 '18 at 7:38




















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53312550%2fformatting-true-false-in-sql%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Florida Star v. B. J. F.

            Image
            Florida Star v. B. J. F. From Wikipedia, the free encyclopedia Jump to navigation Jump to search United States Supreme Court case Florida Star v. B. J. F. Supreme Court of the United States Argued March 21, 1989 Decided June 21, 1989 Full case name The Florida Star v. B. J. F. Citations 491 U.S. 524 ( more ) 109 S. Ct. 2603; 105 L. Ed. 2d 443; 1989 U.S. LEXIS 3120; 57 U.S.L.W. 4816; 16 Media L. Rep. 1801 Prior history The Florida Star v. B.J.F., 530 So.2d 286 (1988) Supreme Court of Florida; Florida Star v. B.J.F., 499 So.2d 883 (1986) Fla. Dist. Court of Appeals Holding Florida Stat. § 794.03 is unconstitutional to the extent it makes the truthful reporting of information that was a matter of public record unlawful, as it violates the First Amendment. Court membership Chief Justice William Rehnquist Associate Justices William J. Brennan Jr.  · Byron White Thurgood Marshall  · Harry Blac
            Read more

            Error while running script in elastic search , gateway timeout

            Image
            0 while running script in elastic search, I got 504 Gateway timeout error. { "query": { "bool": { "filter": { "script": { "script": " doc['creted_date'].date.getMonthOfYear() == 12 " } } } }, "aggs": { "test": { "date_histogram": { "field": "creted_date", "interval": "month", "format": "MMM" }, "aggs": { "cost": { "sum": { "field": "cost" } } }
            Read more

            Adding quotations to stringified JSON object values

            Image
            0 My stringified object data doesn't include quotation marks around object values, which errors when attempting JSON.parse() : '{ "affiliation": CORPORATE, "userId": 75c35d1c-5d12-4485-8fa8-b2f1551a3e6e }' I need the string to be: '{ "affiliation": "CORPORATE", "userId": "75c35d1c-5d12-4485-8fa8-b2f1551a3e6e" }' I'm using this regex to add quotes to the object keys: var newStr = str.replace(/(['"])?([a-z0-9A-Z_]+)(['"])?:/g, '"$2": '); For instance: '{ affiliation: CORPORATE }' to '{ "affiliation": CORPORATE }' There are only string values in my data, so I don't need to check value types. How can I alter my regex expression to add quotations to
            Read more