How to extract data from a JSON field store in bigquery










0















I have this JSON in a bigquery table



"a":"b","superParams": "isTest": "false"


And I would like to select superParas inner JSON



When I run this
select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.a')



I get
a



When I run this
select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.superParams')



I get
null
Instead of "isTest":"false"










share|improve this question




























    0















    I have this JSON in a bigquery table



    "a":"b","superParams": "isTest": "false"


    And I would like to select superParas inner JSON



    When I run this
    select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.a')



    I get
    a



    When I run this
    select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.superParams')



    I get
    null
    Instead of "isTest":"false"










    share|improve this question


























      0












      0








      0








      I have this JSON in a bigquery table



      "a":"b","superParams": "isTest": "false"


      And I would like to select superParas inner JSON



      When I run this
      select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.a')



      I get
      a



      When I run this
      select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.superParams')



      I get
      null
      Instead of "isTest":"false"










      share|improve this question
















      I have this JSON in a bigquery table



      "a":"b","superParams": "isTest": "false"


      And I would like to select superParas inner JSON



      When I run this
      select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.a')



      I get
      a



      When I run this
      select JSON_EXTRACT_SCALAR('"a":"b",superParams":"isTest":"false"','$.superParams')



      I get
      null
      Instead of "isTest":"false"







      google-bigquery






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 14 '18 at 9:38







      Tamir Klein

















      asked Nov 14 '18 at 9:13









      Tamir KleinTamir Klein

      712719




      712719






















          2 Answers
          2






          active

          oldest

          votes


















          0














          The problem is that superParams is an object, not a scalar, so you need to use the JSON_EXTRACT function instead. Try this:



          SELECT JSON_EXTRACT('"a":"b","superParams":"isTest":"false"','$.superParams')





          share|improve this answer

























          • Thanks to @dsesto for pointing out that superParams was missing the quote on the left in the original post.

            – Elliott Brossard
            Nov 14 '18 at 12:43











          • Please mark one of the answers as accepted, then.

            – Elliott Brossard
            Nov 18 '18 at 12:27


















          0














          In case if your intention was to actually extract value for example of isTest parameter - use below



          SELECT JSON_EXTRACT_SCALAR('"a":"b","superParams":"isTest":"false"','$.superParams.isTest')





          share|improve this answer






















            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%2f53296571%2fhow-to-extract-data-from-a-json-field-store-in-bigquery%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









            0














            The problem is that superParams is an object, not a scalar, so you need to use the JSON_EXTRACT function instead. Try this:



            SELECT JSON_EXTRACT('"a":"b","superParams":"isTest":"false"','$.superParams')





            share|improve this answer

























            • Thanks to @dsesto for pointing out that superParams was missing the quote on the left in the original post.

              – Elliott Brossard
              Nov 14 '18 at 12:43











            • Please mark one of the answers as accepted, then.

              – Elliott Brossard
              Nov 18 '18 at 12:27















            0














            The problem is that superParams is an object, not a scalar, so you need to use the JSON_EXTRACT function instead. Try this:



            SELECT JSON_EXTRACT('"a":"b","superParams":"isTest":"false"','$.superParams')





            share|improve this answer

























            • Thanks to @dsesto for pointing out that superParams was missing the quote on the left in the original post.

              – Elliott Brossard
              Nov 14 '18 at 12:43











            • Please mark one of the answers as accepted, then.

              – Elliott Brossard
              Nov 18 '18 at 12:27













            0












            0








            0







            The problem is that superParams is an object, not a scalar, so you need to use the JSON_EXTRACT function instead. Try this:



            SELECT JSON_EXTRACT('"a":"b","superParams":"isTest":"false"','$.superParams')





            share|improve this answer















            The problem is that superParams is an object, not a scalar, so you need to use the JSON_EXTRACT function instead. Try this:



            SELECT JSON_EXTRACT('"a":"b","superParams":"isTest":"false"','$.superParams')






            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 14 '18 at 11:05









            dsesto

            4,35421226




            4,35421226










            answered Nov 14 '18 at 10:20









            Elliott BrossardElliott Brossard

            17.2k21137




            17.2k21137












            • Thanks to @dsesto for pointing out that superParams was missing the quote on the left in the original post.

              – Elliott Brossard
              Nov 14 '18 at 12:43











            • Please mark one of the answers as accepted, then.

              – Elliott Brossard
              Nov 18 '18 at 12:27

















            • Thanks to @dsesto for pointing out that superParams was missing the quote on the left in the original post.

              – Elliott Brossard
              Nov 14 '18 at 12:43











            • Please mark one of the answers as accepted, then.

              – Elliott Brossard
              Nov 18 '18 at 12:27
















            Thanks to @dsesto for pointing out that superParams was missing the quote on the left in the original post.

            – Elliott Brossard
            Nov 14 '18 at 12:43





            Thanks to @dsesto for pointing out that superParams was missing the quote on the left in the original post.

            – Elliott Brossard
            Nov 14 '18 at 12:43













            Please mark one of the answers as accepted, then.

            – Elliott Brossard
            Nov 18 '18 at 12:27





            Please mark one of the answers as accepted, then.

            – Elliott Brossard
            Nov 18 '18 at 12:27













            0














            In case if your intention was to actually extract value for example of isTest parameter - use below



            SELECT JSON_EXTRACT_SCALAR('"a":"b","superParams":"isTest":"false"','$.superParams.isTest')





            share|improve this answer



























              0














              In case if your intention was to actually extract value for example of isTest parameter - use below



              SELECT JSON_EXTRACT_SCALAR('"a":"b","superParams":"isTest":"false"','$.superParams.isTest')





              share|improve this answer

























                0












                0








                0







                In case if your intention was to actually extract value for example of isTest parameter - use below



                SELECT JSON_EXTRACT_SCALAR('"a":"b","superParams":"isTest":"false"','$.superParams.isTest')





                share|improve this answer













                In case if your intention was to actually extract value for example of isTest parameter - use below



                SELECT JSON_EXTRACT_SCALAR('"a":"b","superParams":"isTest":"false"','$.superParams.isTest')






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 14 '18 at 15:36









                Mikhail BerlyantMikhail Berlyant

                60.6k43771




                60.6k43771



























                    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%2f53296571%2fhow-to-extract-data-from-a-json-field-store-in-bigquery%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

                    Use pre created SQLite database for Android project in kotlin

                    Darth Vader #20

                    Ondo