Can i create database tables from an API response or from pasted JSON?

Jay

Working with a new external API and various calls return complex JSON responses with 50+ objects, which I need to save. 

Creating a database table for one of these let alone many would be tedious. 

Is there a way to take the response and turn it into a table structure? Like pasting the JSON somewhere at least? 

Jay

Ended up converting the JSON to CSV, leaving just the header row, and importing using CSV file, which created the table. 

But since the JSON had nested objects, it 
"flattened" the table out, so this is less optimal. Might still have to manually break down sub-objects to sub CSV files and have to connect things manually, which is why I think a smarter auto-table would be great.

Michael Udinski

Unless importing from CSV, you must set up your database table manually so you can tell the API where to map the data to

Jay

 

My attempt to convert the JSON to CSV to import did not work out well. 

All the types of variables got lost in the conversation, not to mention the sub structure like nested objects and arrays. 

Here is an example of a single API response:
{
"is_employee": false,
"seen_layout_switch": false,
"has_visited_new_profile": false,
"pref_no_profanity": true,
"has_external_account": false,
"pref_geopopular": "",
"seen_redesign_modal": true,
"pref_show_trending": true,
"subreddit": {
"default_set": true,
"user_is_contributor": false,
"banner_img": "",
"restrict_posting": true,
"user_is_banned": false,
"free_form_reports": true,
"community_icon": null,
"show_media": true,
"icon_color": "",
"user_is_muted": false,
"display_name": "u_ParadoxApps",
"header_img": null,
"title": "",
"coins": 0,
"previous_names": [],
"over_18": false,
"icon_size": [
256,
256
],
"primary_color": "",
"icon_img": "https://styles.redditmedia.com/t5_2n6id7/styles/profileIcon_snoo1bd4c473-3e3f-4a24-9017-32c31a1d84d2-headshot.png?width=256&height=256&crop=256:256,smart&s=abaf8c04ce091c612d644fe7d1eac560f893af83",
"description": "",
"submit_link_label": "",
"header_size": null,
"restrict_commenting": false,
"subscribers": 0,
"submit_text_label": "",
"is_default_icon": false,
"link_flair_position": "",
"display_name_prefixed": "u/ParadoxApps",
"key_color": "",
"name": "t5_2n6id7",
"is_default_banner": true,
"url": "/user/ParadoxApps/",
"quarantine": false,
"banner_size": null,
"user_is_moderator": true,
"accept_followers": true,
"public_description": "",
"link_flair_enabled": false,
"disable_contributor_requests": false,
"subreddit_type": "user",
"user_is_subscriber": false
},
"pref_show_presence": true,
"snoovatar_img": "https://i.redd.it/snoovatar/avatars/1bd4c473-3e3f-4a24-9017-32c31a1d84d2.png",
"snoovatar_size": [
380,
600
],
"gold_expiration": null,
"has_gold_subscription": false,
"is_sponsor": false,
"num_friends": 0,
"features": {
"mod_service_mute_writes": true,
"promoted_trend_blanks": true,
"show_amp_link": true,
"chat": true,
"is_email_permission_required": false,
"mod_awards": true,
"mweb_xpromo_revamp_v3": {
"owner": "growth",
"variant": "control_2",
"experiment_id": 480
},
"mweb_xpromo_revamp_v2": {
"owner": "growth",
"variant": "treatment_2",
"experiment_id": 457
},
"awards_on_streams": true,
"mweb_xpromo_modal_listing_click_daily_dismissible_ios": true,
"chat_subreddit": true,
"cookie_consent_banner": true,
"modlog_copyright_removal": true,
"do_not_track": true,
"mod_service_mute_reads": true,
"chat_user_settings": true,
"use_pref_account_deployment": true,
"mweb_xpromo_interstitial_comments_ios": true,
"mweb_xpromo_modal_listing_click_daily_dismissible_android": true,
"premium_subscriptions_table": true,
"mweb_xpromo_interstitial_comments_android": true,
"noreferrer_to_noopener": true,
"chat_group_rollout": true,
"resized_styles_images": true,
"spez_modal": true,
"mweb_sharing_clipboard": {
"owner": "growth",
"variant": "control_2",
"experiment_id": 315
},
"expensive_coins_package": true
},
"can_edit_name": false,
"verified": true,
"new_modmail_exists": false,
"pref_autoplay": true,
"coins": 0,
"has_paypal_subscription": false,
"has_subscribed_to_premium": false,
"id": "6et1sz8d",
"has_stripe_subscription": false,
"oauth_client_id": "xxxxxxxx",
"can_create_subreddit": true,
"over_18": false,
"is_gold": false,
"is_mod": true,
"awarder_karma": 0,
"suspension_expiration_utc": null,
"has_verified_email": true,
"is_suspended": false,
"pref_video_autoplay": true,
"in_chat": true,
"has_android_subscription": false,
"in_redesign_beta": true,
"icon_img": "https://styles.redditmedia.com/t5_2n6id7/styles/profileIcon_snoo1bd4c473-3e3f-4a24-9017-32c31a1d84d2-headshot.png?width=256&height=256&crop=256:256,smart&s=abaf8c04ce091c612d644fe7d1eac560f893af83",
"has_mod_mail": false,
"pref_nightmode": true,
"awardee_karma": 0,
"hide_from_robots": false,
"password_set": true,
"link_karma": 1,
"force_password_reset": false,
"total_karma": 1,
"seen_give_award_tooltip": false,
"inbox_count": 1,
"seen_premium_adblock_modal": false,
"pref_top_karma_subreddits": true,
"has_mail": true,
"pref_show_snoovatar": false,
"name": "ParadoxApps",
"pref_clickgadget": 5,
"created": 1588873926,
"gold_creddits": 0,
"created_utc": 1588873926,
"has_ios_subscription": false,
"pref_show_twitter": false,
"in_beta": false,
"comment_karma": 0,
"accept_followers": true,
"has_subscribed": true,
"linked_identities": [],
"seen_subreddit_chat_ftux": false
}
To create a matching database table for this single response, let alone for all of the other API calls would take forever. 

Is there really no way to create/update database structure within a function, or any other solution for this?

Jay

I ended up only starting from the fields i want to work with first, but maybe turn this into a feature request. This would be extremely useful in many use cases. At least being able to create/change table and table columns inside functions.