i'm trying to extract two key from every json in an arry of jsons(using sql legacy) currently i am using json extract function :
json_extract(json_column , '$[1].X') AS X,
json_extract(json_column , '$[1].Y') AS Y,
how can i make it run on every json at the 'json arry column', and not just [1] (for example)?
An example json:
[
{"blabla":000,"X":1,"blabla":000,"blabla":000,"blabla":000,,"Y":"2"},
{"blabla":000,"X":3,"blabla":000,"blabla":000,"blabla":000,,"Y":"4"},
]
thanks in advance!
Let's start with a similar problem - this is not a very convenient way to extract all emails from a json array:
The best way we have right now to deal with this is to use some JavaScript in an UDF to split a json-array into a SQL array:
Below example for BigQuery Standard SQL and allows you to be close to standard way of working with JSONPath and no extra manipulation needed so you just simply use
CUSTOM_JSON_EXTRACT(json, json_path)
functionresult will be
Note: to overcome current BigQuery's "limitation" for JsonPath, above solution uses custom function along with external library -
jsonpath-0.8.0.js
that can be downloaded from https://code.google.com/archive/p/jsonpath/downloads and uploaded to Google Cloud Storage - gs://your_bucket/jsonpath-0.8.0.jsJust re-read Felipe's answer - for his example above solution will look like below (just as FYI)