0

I'm currently having trouble extracting data from a JSON String.

The way the data has been pulled, everything has been nested into a single string under the data field name.

How it looks like in Big Query:

Screenshot of the Schema:

enter image description here

Below is an example of what the string looks like:

{"id":1381,"email":"J.Smith@gmail.com","name":"Jake Smith","sub_network_ids":[2375,2270],"extended_updated_at":"2022-01-27T00:02:14Z"}

If I simply wanted to pull the ID, Email, and Name from this string and into a table, I'm wondering how would one go about doing such? Currently, I was trying to use JSON_EXTRACT with Unnest, but that didn't pan out in the direction I thought it would.

Any help would be appreciated, thanks.

lemon
  • 14,875
  • 6
  • 18
  • 38
A.Y. Greyson
  • 1
  • Would you try to start with `JSON_VALUE(json, '$.id')` , `JSON_VALUE(json, '$.email')` and so on ? `JSON_EXTRACT` is normally used in other situation like when you want to extract a nested json, for example. – Jaytiger Jun 07 '22 at 09:33
  • For extracting array, consider this `JSON_VALUE_ARRAY(json, '$.sub_network_ids')` – Jaytiger Jun 07 '22 at 09:37

0 Answers0