# Need help extracting JSON objects to separate columns in CloudQuery **URL:** \ **Category:** CloudQuery Plugins\ **Created:** [August 28, 2023, 12:45pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80 "2023-08-28T12:45:44Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![sure-hound](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@sure-hound](https://community.cloudquery.io/u/sure-hound)\ **Post date:** [August 28, 2023, 12:45pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/1 "2023-08-28T12:45:44Z")
Maybe it’s just my lack of strong SQL skills that is hurting me here, but the biggest challenge we’re running into is trying to accurately extract/expand all these JSON objects that are being written to the DB (BigQuery in our case) to the point where I’m considering adding another ETL tool (a la DBT or similar) on top of this data collection just so I can unravel all this JSON into its own column. But before I go down that very painful route, I figured I’d ask/make a suggestion… Is CloudQuery considering any feature/update that will start parsing these JSON objects and writing them as individual columns? ---
**Author:** ![handy-bee](https://avatars.discourse-cdn.com/v4/letter/h/8e7dd6/32.png) [@handy-bee](https://community.cloudquery.io/u/handy-bee)\ **Post date:** [August 28, 2023, 12:55pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/2 "2023-08-28T12:55:39Z")
Yeah, we ran into similar issues and added DBT into the mix, but really CQ is the E and the L of data, not so much the T. And that’s where tools like DBT shine, in my opinion. The searchable table documentation alone makes it very worth it. Though I’m curious what you had in mind for column extraction? What we found is that initial JSON syntax familiarization was weird for analysts, but once they understood, it was easier. Plus, with all the various JSON structures across all the different providers, I was worried we’d end up having to manage all our own schemas/tables for things we rarely used more than once. ---
**Author:** ![sure-hound](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@sure-hound](https://community.cloudquery.io/u/sure-hound)\ **Post date:** [August 28, 2023, 1:01pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/3 "2023-08-28T13:01:08Z")
I will say we’re _mostly_ GCP, so I’m a bit less worried (maybe inaccurately) about trying to maintain a schema ourselves in that regard. But as an example that I’ve been working on this morning… ```sql gcp_storage_bucket_policies ``` table returns a column `bindings` which is structured something like: ```json [ {members: [lol, lol2], role: lolrole}, {members: [lol3, lol4], role: lolrole2} ] ``` So I guess in my mind, there would be something like `bindings.role` and `bindings.members` columns (or whatever the names would be) that extract everything out into ordered pairs/arrays? So like `bindings.members[0]` and `bindings.role[0]` are the matching element pairs? TBH - I’m much better at complaining than figuring out good solutions 😂 but this JSON is whopping my ass right now. ---
**Author:** ![pilvikala](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/pilvikala/32/11_2.png) [@pilvikala](https://community.cloudquery.io/u/pilvikala)\ **Post date:** [August 28, 2023, 1:06pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/4 "2023-08-28T13:06:02Z")
@sure-hound Thanks for the feedback! I’d like to better understand what you were trying to do and see if there’s some low-hanging fruit we could pick. Would you have some time for a quick chat with me and the team this week? ---
**Author:** ![sure-hound](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@sure-hound](https://community.cloudquery.io/u/sure-hound)\ **Post date:** [August 28, 2023, 1:09pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/5 "2023-08-28T13:09:00Z")
@pilvikala - Absolutely! Anything to help give back 😄 ---
**Author:** ![pilvikala](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/pilvikala/32/11_2.png) [@pilvikala](https://community.cloudquery.io/u/pilvikala)\ **Post date:** [August 28, 2023, 1:09pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/6 "2023-08-28T13:09:18Z")
I’ll DM you with my calendar link 🙂 ---
**Author:** ![handy-bee](https://avatars.discourse-cdn.com/v4/letter/h/8e7dd6/32.png) [@handy-bee](https://community.cloudquery.io/u/handy-bee)\ **Post date:** [August 28, 2023, 1:19pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/7 "2023-08-28T13:19:32Z")
I think some of these could easily be expanded to child tables within the provider. ---
**Author:** ![sure-hound](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@sure-hound](https://community.cloudquery.io/u/sure-hound)\ **Post date:** [August 28, 2023, 1:44pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/8 "2023-08-28T13:44:23Z")
I think some of these could easily be expanded to child tables within the provider, so something like `gcp_storage_bucket_policies_bindings` where it’s just columns of `members, role`? ---
**Author:** ![handy-bee](https://avatars.discourse-cdn.com/v4/letter/h/8e7dd6/32.png) [@handy-bee](https://community.cloudquery.io/u/handy-bee)\ **Post date:** [August 28, 2023, 2:28pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/9 "2023-08-28T14:28:42Z")
Something like that, yeah. Are all the JSON objects identical to that? If it’s just those two properties in that JSON blob, though, perhaps just a new resolver within the fetching function is all that’s needed. I think most of the GCP CQ SDK was autogenerated, so it might just be that no one’s hit the issue before. I’d agree that “list based” JSON (stuff that is wrapped in square brackets) is very irritating to work with. ---
**Author:** ![closing-mink](https://avatars.discourse-cdn.com/v4/letter/c/87869e/32.png) [@closing-mink](https://community.cloudquery.io/u/closing-mink)\ **Post date:** [October 26, 2023, 7:35pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/10 "2023-10-26T19:35:18Z")
Unpacking JSON offends me too. Busy work. We have some static dbt models that unpack generators. The [bigquery-schema-generator](https://pypi.org/project/bigquery-schema-generator/) is useful. My preference would be for CloudQuery to include a configuration option for schema evolution: **On Success:** - Add new columns as type/name evolve - Raise warning when new columns are added **On Failure:** - Dump payload to object storage - Raise error - Mechanism to restart from object storage Does it feel cluttered for every ingestor app to have a recovery partner app that has object storage as the input? I’d prefer this to be a standard pattern. ---
**Author:** ![pilvikala](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/pilvikala/32/11_2.png) [@pilvikala](https://community.cloudquery.io/u/pilvikala)\ **Post date:** [October 27, 2023, 9:11am UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/11 "2023-10-27T09:11:37Z")
@closing-mink Thanks for sharing on how you approached this! Just to make sure I understand your proposal: Are you suggesting to expand each key/value pair into a column named by the key? How do you approach expanding JSON objects with multiple levels or arrays? ---
**Author:** ![sure-hound](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@sure-hound](https://community.cloudquery.io/u/sure-hound)\ **Post date:** [October 27, 2023, 12:30pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/12 "2023-10-27T12:30:55Z")
Actually ran into an even worse table in terms of parsing JSON (`gcp_compute_osconfig_inventories`). But what I’ve just started doing is creating user defined functions (it’s a BigQuery thing, I think other SQL DBs called them stored procedures) that parse these JSON objects. In BigQuery, you can actually run JavaScript in your UDF, so I’m just passing the column into a bunch of JavaScript and parsing it there. This way I’m not having to maintain an ETL process outside of that. For example: ```sql CREATE TEMP FUNCTION extract_package(json STRING) RETURNS ARRAY> LANGUAGE js AS """ let data = JSON.parse(json); let result = []; for (const key in data) { let package_name, version, architecture; if (data[key]['Details']['InstalledPackage']['Details']['CosPackage']) { package_name = data[key]['Details']['InstalledPackage']['Details']['CosPackage']['package_name']; version = data[key]['Details']['InstalledPackage']['Details']['CosPackage']['version']; architecture = data[key]['Details']['InstalledPackage']['Details']['CosPackage']['architecture']; } else if (data[key]['Details']['AvailablePackage']['Details']['AptPackage']) { package_name = data[key]['Details']['AvailablePackage']['Details']['AptPackage']['package_name']; version = data[key]['Details']['AvailablePackage']['Details']['AptPackage']['version']; architecture = data[key]['Details']['AvailablePackage']['Details']['AptPackage']['architecture']; } result.push({ package_name, version, architecture }); } return result; """; SELECT DISTINCT project_id AS `Project`, STRING(os_info.hostname) AS `Compute Name`, STRING(os_info.short_name) AS `Operating System`, STRING(os_info.version) AS `OS Version`, SPLIT(pkgs.package_name, '/')[SAFE_OFFSET(1)] AS `Package Name`, pkgs.version AS `Package Version`, pkgs.architecture AS `Package Arch` FROM `cloudquery.gcp_compute_osconfig_inventories`, UNNEST(extract_package(TO_JSON_STRING(items))) pkgs WHERE TIMESTAMP_TRUNC(_cq_sync_time, DAY) = TIMESTAMP("2023-10-25") LIMIT 1 ``` Now all I have to do is iterate that JSON column to find all variations of the JSON, add it to the `if else` loop, and move on with life. Saving me from having to maintain additional infrastructure 😅 ---
**Author:** ![closing-mink](https://avatars.discourse-cdn.com/v4/letter/c/87869e/32.png) [@closing-mink](https://community.cloudquery.io/u/closing-mink)\ **Post date:** [October 27, 2023, 9:40pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/13 "2023-10-27T21:40:30Z")
We have some tables that land JSON and then use the BigQuery JSON functions in dbt to parse. [BigQuery JSON Functions Documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions) Like: ```auto SELECT STRUCT( JSON_VALUE(json_data.path.to.foo) AS foo, JSON_VALUE_ARRAY(json_data.path.to.`myArray`) AS my_array ) AS my_struct FROM foo ``` It works, but it’s getting to the starting position, not a value add. ---
**Author:** ![pilvikala](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/pilvikala/32/11_2.png) [@pilvikala](https://community.cloudquery.io/u/pilvikala)\ **Post date:** [October 30, 2023, 12:35pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/14 "2023-10-30T12:35:18Z")
@closing-mink Would this be a good solution? [GitHub - cloudquery/cloudquery#9931](https://github.com/cloudquery/cloudquery/issues/9931) ---
**Author:** ![closing-mink](https://avatars.discourse-cdn.com/v4/letter/c/87869e/32.png) [@closing-mink](https://community.cloudquery.io/u/closing-mink)\ **Post date:** [October 30, 2023, 10:16pm UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/15 "2023-10-30T22:16:06Z")
Yes. I imagine a flag in the destination config something like `parse_json` as a boolean with default `false` (backwards compatibility) and/or a path to a `schema.json` file for more complex selection (e.g., `key_a` is a struct, `key_b` is JSON). ---
**Author:** ![pilvikala](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/pilvikala/32/11_2.png) [@pilvikala](https://community.cloudquery.io/u/pilvikala)\ **Post date:** [October 31, 2023, 8:46am UTC](https://community.cloudquery.io/t/need-help-extracting-json-objects-to-separate-columns-in-cloudquery/80/16 "2023-10-31T08:46:46Z")
@closing-mink Great, thank you for the feedback. Could you please upvote that issue? 🙂