I am trying to load data into bigquery table by doing an API Query. The JSON response i get is in this format. I tried to use values as a repeating element but it does not work as its not repeated except once. How to load such data?
{
"extrapolationLevel": 1,
"columnNames": [
"URL",
"Visually Complete(75th)",
"Speed Index",
"First Input Delay",
"Largest Contentful Paint",
"Cumulative Layout Shift"
],
"values": [
[
"https://www.abcd.com/",
8739,
4522,
null,
8657,
0.3586
],
[
"https://www.efgh.com/",
4210,
697,
null,
1590,
0.0019
],
[
"https://www.jklm.com/abacus",
null,
null,
null,
null,
null
]
]
}
Just to clarify, does the data source require you to use a repeating element? Is it very large? If not, a repeating element isn't really required. Your data may be a little less "structured" though.
Hi Ben, the data source does not require me to use repeating element. I just tried to use "/values" as a repeating element but it returns no columns, since it not a repeating element anyways. You said its a bit less structured, how to process such data? I am only interested in columns W, X, Y, Z to load these into a bigquery table.
{
"ABCD": 1,
"columnNames": [
"W",
"X",
"Y",
"Z"
],
"values": [
[
"https://www.abc.co.th/",
1,
2,
null
],
[
"https://www.abc.co.jp/",
3,
4,
5
]
]
}
If you're using API Query, and all you want to extract is the ColumnNames, then I would suggest just unticking the columns below ColumnNames, and then select ColumnNames as your repeating element. This may work, although I'm struggling to picture what your data structure looks like.
I am actually a Product Owner at Matillion, and would be more than happy to jump on a call to discuss it with you firsthand, if the above suggestion doesn't help.