A method of outputting only records with a specific structure in a bigquery

Asked 1 months ago, Updated 1 months ago, 6 views

Register the following json data as a sample.
For example, to write a sql statement that prints only records with the first line of History structure,
I'd like to get the size of Books.History and compare them. Is there any way to do this?

◆JSON files:

{"User":{"Name":"aaaaa", "age":"20", "Books":{"History":{"Title":"xxxx", "page":"100"}}}}
{"User": {"Name": "aaaaaa", "age": "20"}, "Books": {"Science": {"Title": "yyyyy", "page": "200"}}}}

json google-bigquery

2022-09-30 11:35

1 Answers

Sorry, I solved myself.

The json sample from the time of the question can now produce the expected output of the SQL statement below.

select User,Books.History from `table name`
where Books.History is not null

2022-09-30 11:35

If you have any answers or tips

© 2022 OneMinuteCode. All rights reserved.