How to extract data from a JSON field store in bigquery

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"

edited Nov 14 '18 at 9:38

asked Nov 14 '18 at 9:13

Tamir Klein

712719

add a comment |

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"

edited Nov 14 '18 at 9:38

asked Nov 14 '18 at 9:13

Tamir Klein

712719

add a comment |

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"

edited Nov 14 '18 at 9:38

asked Nov 14 '18 at 9:13

Tamir Klein

712719

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

edited Nov 14 '18 at 9:38

asked Nov 14 '18 at 9:13

Tamir Klein

712719

edited Nov 14 '18 at 9:38

asked Nov 14 '18 at 9:13

Tamir Klein

712719

edited Nov 14 '18 at 9:38

asked Nov 14 '18 at 9:13

Tamir Klein

712719

asked Nov 14 '18 at 9:13

Tamir Klein

712719

asked Nov 14 '18 at 9:13

Tamir Klein

712719

add a comment |

2 Answers
2

active

oldest

votes

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')

edited Nov 14 '18 at 11:05

dsesto

4,35421226

answered Nov 14 '18 at 10:20

Elliott Brossard

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

add a comment |

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')

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

add a comment |

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

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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')

edited Nov 14 '18 at 11:05

dsesto

4,35421226

answered Nov 14 '18 at 10:20

Elliott Brossard

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

add a comment |

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')

edited Nov 14 '18 at 11:05

dsesto

4,35421226

answered Nov 14 '18 at 10:20

Elliott Brossard

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

add a comment |

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')

edited Nov 14 '18 at 11:05

dsesto

4,35421226

answered Nov 14 '18 at 10:20

Elliott Brossard

17.2k21137

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')

edited Nov 14 '18 at 11:05

dsesto

4,35421226

answered Nov 14 '18 at 10:20

Elliott Brossard

17.2k21137

edited Nov 14 '18 at 11:05

dsesto

4,35421226

edited Nov 14 '18 at 11:05

dsesto

4,35421226

edited Nov 14 '18 at 11:05

dsesto

4,35421226

answered Nov 14 '18 at 10:20

Elliott Brossard

17.2k21137

answered Nov 14 '18 at 10:20

Elliott Brossard

17.2k21137

answered Nov 14 '18 at 10:20

Elliott Brossard

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

add a comment |

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

add a comment |

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')

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

add a comment |

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')

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

add a comment |

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')

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

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')

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

answered Nov 14 '18 at 15:36

Mikhail Berlyant

60.6k43771

add a comment |

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Pfthb