CODEWITHSUNDEEP
jsongoogle-bigquery
user4033385
user4033385

Reputation: 553

JSON issue in Big Query

I have scenario to parse the json data which is of one column in table. Issue is that below Response column as json generated by Datastore backup to BigQuery. It has '\'attached to every data. 

Reponse": "[
  {
    \"questionId\":5121566669012992,
    \"answereId\":0,
    \"answeredText\":\"Summer\"
  },{
    \"questionId\":5166851730440192,
    \"answereId\":0,
    \"answeredText\":\"Barcelona\"
  },{
    \"questionId\":6304057064947712,
    \"answereId\":0,
    \"answeredText\":\"Kitesurf\"
  }
]"

How do I parse the below to get value for questionId using BigQuery?

Upvotes: 2

Views: 175

Answers (1)

Pentium10
Pentium10

Reputation: 208042

JSON_EXTRACT cannot return REPEATED field, it can only do one match - hence no support for *

you can get the first position using hardcoded indexes as

SELECT JSON_EXTRACT_SCALAR('[
  {
    \"questionId\":5121566669012992,
    \"answereId\":0,
    \"answeredText\":\"Summer\"
  },{
    \"questionId\":5166851730440192,
    \"answereId\":0,
    \"answeredText\":\"Barcelona\"
  },{
    \"questionId\":6304057064947712,
    \"answereId\":0,
    \"answeredText\":\"Kitesurf\"
  } 
]', '$[0].questionId') AS str;

This returns:

+-----+------------------+---+
| Row |       str        |   |
+-----+------------------+---+
|   1 | 5121566669012992 |   |
+-----+------------------+---+

Upvotes: 1

Related Questions