# Unable to load data from API query due to JSON data format

**URL:** <https://matillioncommunity.discourse.group/t/unable-to-load-data-from-api-query-due-to-json-data-format/2023>\
**Category:** Matillion ETL\
**Created:** [January 13, 2023, 9:33pm UTC](https://matillioncommunity.discourse.group/t/unable-to-load-data-from-api-query-due-to-json-data-format/2023 "2023-01-13T21:33:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![PatrickS](https://avatars.discourse-cdn.com/v4/letter/p/ac91a4/32.png) [@PatrickS](https://matillioncommunity.discourse.group/u/PatrickS)\
**Post date:** [January 13, 2023, 9:33pm UTC](https://matillioncommunity.discourse.group/t/unable-to-load-data-from-api-query-due-to-json-data-format/2023/1 "2023-01-13T21:33:38Z")

</div>

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?

{

&nbsp;"extrapolationLevel": 1,

&nbsp;"columnNames": [

&nbsp;&nbsp;"URL",

&nbsp;&nbsp;"Visually Complete(75th)",

&nbsp;&nbsp;"Speed Index",

&nbsp;&nbsp;"First Input Delay",

&nbsp;&nbsp;"Largest Contentful Paint",

&nbsp;&nbsp;"Cumulative Layout Shift"

&nbsp;],

&nbsp;"values": [

&nbsp;&nbsp;[

&nbsp;&nbsp;&nbsp;"https://www.abcd.com/",

&nbsp;&nbsp;&nbsp;8739,

&nbsp;&nbsp;&nbsp;4522,

&nbsp;&nbsp;&nbsp;null,

&nbsp;&nbsp;&nbsp;8657,

&nbsp;&nbsp;&nbsp;0.3586

&nbsp;&nbsp;],

&nbsp;&nbsp;[

&nbsp;&nbsp;&nbsp;"https://www.efgh.com/",

&nbsp;&nbsp;&nbsp;4210,

&nbsp;&nbsp;&nbsp;697,

&nbsp;&nbsp;&nbsp;null,

&nbsp;&nbsp;&nbsp;1590,

&nbsp;&nbsp;&nbsp;0.0019

&nbsp;&nbsp;],

&nbsp;&nbsp;[

&nbsp;&nbsp;&nbsp;"https://www.jklm.com/abacus",

&nbsp;&nbsp;&nbsp;null,

&nbsp;&nbsp;&nbsp;null,

&nbsp;&nbsp;&nbsp;null,

&nbsp;&nbsp;&nbsp;null,

&nbsp;&nbsp;&nbsp;null

&nbsp;&nbsp;]

&nbsp;]

}

---

<div class="post-metadata">

**Author:** ![BenSmilesMatillionProductOwner](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@BenSmilesMatillionProductOwner](https://matillioncommunity.discourse.group/u/BenSmilesMatillionProductOwner)\
**Post date:** [January 16, 2023, 8:34am UTC](https://matillioncommunity.discourse.group/t/unable-to-load-data-from-api-query-due-to-json-data-format/2023/2 "2023-01-16T08:34:52Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![PatrickS](https://avatars.discourse-cdn.com/v4/letter/p/ac91a4/32.png) [@PatrickS](https://matillioncommunity.discourse.group/u/PatrickS)\
**Post date:** [January 17, 2023, 4:05am UTC](https://matillioncommunity.discourse.group/t/unable-to-load-data-from-api-query-due-to-json-data-format/2023/3 "2023-01-17T04:05:35Z")

</div>

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.

{

&nbsp;"ABCD": 1,

&nbsp; "columnNames": [

"W",

&nbsp;&nbsp; "X",

&nbsp; &nbsp;"Y",

&nbsp; &nbsp;"Z" &nbsp;

],

"values": [

&nbsp;&nbsp;[

&nbsp; &nbsp;&nbsp;"https://www.abc.co.th/",

&nbsp; &nbsp;&nbsp;1,

&nbsp;&nbsp; &nbsp;2,

&nbsp;&nbsp;&nbsp; null

],

[

&nbsp;"https://www.abc.co.jp/",

&nbsp; &nbsp;&nbsp;3,

&nbsp;&nbsp; 4,

&nbsp; &nbsp;&nbsp;5

]

&nbsp;]

}

---

<div class="post-metadata">

**Author:** ![BenSmilesMatillionProductOwner](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@BenSmilesMatillionProductOwner](https://matillioncommunity.discourse.group/u/BenSmilesMatillionProductOwner)\
**Post date:** [January 17, 2023, 8:39am UTC](https://matillioncommunity.discourse.group/t/unable-to-load-data-from-api-query-due-to-json-data-format/2023/4 "2023-01-17T08:39:28Z")

</div>

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.

&nbsp;

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.