sqljsonsnowflake-cloud-data-platform
Snowflake - Parsing JSON / Variant Data
So basically I have a table in Snowflake with a column containing data in JSON format (variant col), and these json contains a lot of of key / value pairs (> 130)
Here is the table DDL :
create or replace TABLE DEV_ODS_SOFINORD.ODS.CRM_JSON_VARIANT_ALL (
OFFSET_VAL NUMBER(38,0),
ORIGINE VARCHAR(25),
JSON VARIANT,
DATE_CHARGEMENT TIMESTAMP_NTZ(9)
);
And here is a part of the JSON contained in the variant column
As of now I access this data with the flatten function and a request built like this :
SELECT
SAC.ORIGINE,
SAC.DATE_CHARGEMENT,
js.VALUE:_module::STRING AS _module,
js.VALUE:account_id::STRING AS AT_ACCOUNT_ID,
js.VALUE:account_name::STRING AS AT_ACCOUNT_NAME,
...
FROM DEV_ODS_SOFINORD.ODS.CRM_JSON_VARIANT_ALL SAC,
table (flatten (INPUT => SAC.JSON)) js ,
WHERE SAC.ORIGINE = 'CONTACTS';
This works but it is really tedious to enter each key and such (and I would have to repeat this operation 7 times), so I wanted to know if this was possible to basically apply a function similar to a SELECT to get all the data of the first depth (the data like "_acl", "accounts" is unecessary in my case) without having to write each key like
js.VALUE:_module::STRING AS _module
Thanks,
Edit : JSON Sample + table creation / insert
-- TESTDB.ODS.TEST_JSON_VARIANT definition
create or replace TABLE TESTDB.ODS.TEST_JSON_VARIANT (
ORIGINE VARCHAR(50),
JSON VARIANT,
DATE_CHARGEMENT TIMESTAMP_NTZ(9)
);
INSERT INTO TESTDB.ODS.TEST_JSON_VARIANT (ORIGINE,JSON,DATE_CHARGEMENT) VALUES
('TEST','[
{
"_acl": {
"fields": {}
},
"_module": "Contacts",
"accept_status_calls": "",
"accept_status_id": "",
"accept_status_meetings": "",
"accept_status_messages": "",
"accept_status_name": "",
"account_id": "973e0c06-ecf9-11ee-8e54-d05099f915d8",
"account_name": "TEST",
"accounts": {
"_acl": {
"_hash": "7736b5170dcc226f612bebae1821f44e",
"create": "no",
"delete": "no",
"edit": "no",
"fields": {
"chrono_c": {
"create": "no",
"write": "no"
},
"team_id": {
"create": "no",
"write": "no"
},
"team_name": {
"create": "no",
"write": "no"
}
},
"import": "no",
"massupdate": "no"
},
"id": "973e0c06-ecf9-11ee-8e54-d05099f915d8",
"name": "TEST"
},
"action_marketing_c": [],
"alt_address_city": "",
"alt_address_country": "",
"alt_address_postalcode": "",
"alt_address_state": "",
"alt_address_street": "",
"alt_address_street_2": "",
"alt_address_street_3": "",
"assigned_user_id": "677013ca-b44c-11ed-8103-a4bf01232d4e",
"assigned_user_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST1",
"id": "677013ca-b44c-11ed-8103-a4bf01232d4e"
},
"assigned_user_name": "TEST1",
"assistant": "",
"assistant_phone": "",
"birthdate": "",
"business_center_id": "",
"business_center_name": "",
"business_centers": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"c_accept_status_fields": "",
"calls": {
"id": ""
},
"campaign_contacts": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"campaign_id": "",
"campaign_name": "",
"centre_interets_c": [],
"cookie_consent": false,
"cookie_consent_received_on": "",
"created_by": "677013ca-b44c-11ed-8103-a4bf01232d4e",
"created_by_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST1",
"id": "677013ca-b44c-11ed-8103-a4bf01232d4e"
},
"created_by_name": "TEST1",
"date_entered": "2024-03-28T12:56:20+01:00",
"date_modified": "2024-03-28T12:57:38+01:00",
"decisionnaire_c": "non",
"deleted": false,
"denorm_account_name": "test",
"department": "",
"department_c": "",
"description": "",
"desinscription_marketing_c": "",
"dnb_principal_id": "",
"do_not_call": false,
"dp_business_purpose": [],
"dp_consent_last_updated": "2024-03-28",
"dri_workflow_template_id": "",
"dri_workflow_template_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"dri_workflow_template_name": "",
"email": [
{
"email_address": "[email protected]",
"email_address_id": "30d1dd98-ecfa-11ee-88f6-d05099f915d8",
"invalid_email": false,
"opt_out": false,
"primary_address": true,
"reply_to_address": false
}
],
"email1": "[email protected]",
"email2": "",
"email_addresses_non_primary": "",
"email_and_name1": "",
"email_opt_out": false,
"entry_source": "internal",
"facebook": "",
"filiale_c": [
"ETE"
],
"first_name": "Lou",
"following": false,
"full_name": "Test Name",
"geocode_status": "",
"googleplus": "",
"hint_account_annual_revenue": "",
"hint_account_description": "",
"hint_account_facebook_handle": "",
"hint_account_fiscal_year_end": "",
"hint_account_founded_year": "",
"hint_account_industry": "",
"hint_account_location": "",
"hint_account_logo": "",
"hint_account_naics_code_lbl": "",
"hint_account_sic_code_label": "",
"hint_account_size": "",
"hint_account_twitter_handle": "",
"hint_account_website": "",
"hint_contact_pic": "",
"hint_education": "",
"hint_education_2": "",
"hint_facebook": "",
"hint_industry_tags": "",
"hint_job_2": "",
"hint_phone_1": "",
"hint_phone_2": "",
"hint_photo": "",
"hint_twitter": "",
"id": "30de4150-ecfa-11ee-ad09-d05099f915d8",
"intitule_poste_c": "RRH",
"invalid_email": false,
"last_name": "TEST2",
"lead_source": "",
"locked_fields": [],
"m_accept_status_fields": "",
"mailchimp_rating_c": 2,
"market_interest_prediction_score": "",
"market_score": null,
"meetings": {
"id": ""
},
"mkto_id": null,
"mkto_lead_score": null,
"mkto_sync": false,
"modified_by_name": "TEST TEST1",
"modified_user_id": "677013ca-b44c-11ed-8103-a4bf01232d4e",
"modified_user_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST TEST1",
"id": "677013ca-b44c-11ed-8103-a4bf01232d4e"
},
"my_favorite": false,
"name": "TEST",
"opportunities": {
"id": ""
},
"opportunity_role": "",
"opportunity_role_fields": "",
"opportunity_role_id": "",
"origine_contact_c": [
"poste teste"
],
"phone_fax": "",
"phone_home": "",
"phone_mobile": "",
"phone_other": "",
"phone_work": "00 00 00 00 00",
"picture": "",
"portal_active": false,
"portal_app": "",
"portal_name": "",
"portal_password": null,
"portal_password1": null,
"portal_user_company_name": "",
"preferred_language": "",
"primary_address_city": "Adresse Teste",
"primary_address_country": "100",
"primary_address_postalcode": "99999",
"primary_address_state": "",
"primary_address_street": "20 ROUTE DE TEST",
"primary_address_street_1_c": "20 ROUTE DE TEST",
"primary_address_street_2": "",
"primary_address_street_2_c": "",
"primary_address_street_3": "",
"primary_address_street_3_c": "",
"primary_address_street_4_c": "",
"profil_linkedin_c": "",
"report_to_name": "",
"reports_to_id": "",
"reports_to_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"salutation": "Mrs.",
"score_engagement_c": "",
"site_user_id": "",
"source_id": "",
"source_meta": "",
"source_type": "",
"statut_c": "",
"statut_hubspot_api_c": "",
"sync_contact": false,
"sync_key": "",
"tag": [],
"team_count": "",
"team_count_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "1",
"team_count": ""
},
"team_name": [
{
"id": "1",
"name": "Global",
"name_2": "",
"primary": true,
"selected": false
}
],
"title": "",
"title_c": "assistant",
"twitter": ""
},
{
"_acl": {
"fields": {}
},
"_module": "Contacts",
"accept_status_calls": "",
"accept_status_id": "",
"accept_status_meetings": "",
"accept_status_messages": "",
"accept_status_name": "",
"account_id": "617ff1d0-ecf2-11ee-82d8-d05099f915d8",
"account_name": "TEST2",
"accounts": {
"_acl": {
"_hash": "7736b5170dcc226f612bebae1821f44e",
"create": "no",
"delete": "no",
"edit": "no",
"fields": {
"chrono_c": {
"create": "no",
"write": "no"
},
"team_id": {
"create": "no",
"write": "no"
},
"team_name": {
"create": "no",
"write": "no"
}
},
"import": "no",
"massupdate": "no"
},
"id": "617ff1d0-ecf2-11ee-82d8-d05099f915d8",
"name": "TEST2"
},
"action_marketing_c": [],
"alt_address_city": "",
"alt_address_country": "",
"alt_address_postalcode": "",
"alt_address_state": "",
"alt_address_street": "",
"alt_address_street_2": "",
"alt_address_street_3": "",
"assigned_user_id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c",
"assigned_user_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "MarlÚne GAILLARD",
"id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c"
},
"assigned_user_name": "MarlÚne GAILLARD",
"assistant": "",
"assistant_phone": "",
"birthdate": "",
"business_center_id": "",
"business_center_name": "",
"business_centers": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"c_accept_status_fields": "",
"calls": {
"id": ""
},
"campaign_contacts": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"campaign_id": "",
"campaign_name": "",
"centre_interets_c": [],
"cookie_consent": false,
"cookie_consent_received_on": "",
"created_by": "cfcff18e-56bc-11e9-885b-0cc47a6d326c",
"created_by_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST TEST2",
"id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c"
},
"created_by_name": "TEST TEST2",
"date_entered": "2024-03-28T12:05:18+01:00",
"date_modified": "2024-03-28T12:05:18+01:00",
"decisionnaire_c": "oui",
"deleted": false,
"denorm_account_name": "TEST TEST2",
"department": "",
"department_c": "affaire",
"description": "",
"desinscription_marketing_c": "",
"dnb_principal_id": "",
"do_not_call": false,
"dp_business_purpose": [],
"dp_consent_last_updated": "2024-03-28",
"dri_workflow_template_id": "",
"dri_workflow_template_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"dri_workflow_template_name": "",
"email": [
{
"email_address": "[email protected]",
"email_address_id": "102f914a-ecf3-11ee-a712-d05099f915d8",
"invalid_email": false,
"opt_out": false,
"primary_address": true,
"reply_to_address": false
}
],
"email1": "[email protected]",
"email2": "",
"email_addresses_non_primary": "",
"email_and_name1": "",
"email_opt_out": false,
"entry_source": "internal",
"facebook": "",
"filiale_c": [
"E0TT3"
],
"first_name": "TEST2",
"following": false,
"full_name": "MME TEST2",
"geocode_status": "",
"googleplus": "",
"hint_account_annual_revenue": "",
"hint_account_description": "",
"hint_account_facebook_handle": "",
"hint_account_fiscal_year_end": "",
"hint_account_founded_year": "",
"hint_account_industry": "",
"hint_account_location": "",
"hint_account_logo": "",
"hint_account_naics_code_lbl": "",
"hint_account_sic_code_label": "",
"hint_account_size": "",
"hint_account_twitter_handle": "",
"hint_account_website": "",
"hint_contact_pic": "",
"hint_education": "",
"hint_education_2": "",
"hint_facebook": "",
"hint_industry_tags": "",
"hint_job_2": "",
"hint_phone_1": "",
"hint_phone_2": "",
"hint_photo": "",
"hint_twitter": "",
"id": "103d1432-ecf3-11ee-829e-d05099f915d8",
"intitule_poste_c": "property manager",
"invalid_email": false,
"last_name": "TEST2",
"lead_source": "",
"locked_fields": [],
"m_accept_status_fields": "",
"mailchimp_rating_c": 2,
"market_interest_prediction_score": "",
"market_score": null,
"meetings": {
"id": ""
},
"mkto_id": null,
"mkto_lead_score": null,
"mkto_sync": false,
"modified_by_name": "TEST TEST2",
"modified_user_id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c",
"modified_user_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST TEST 2",
"id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c"
},
"my_favorite": false,
"name": "Mme TEST TEST 2",
"opportunities": {
"id": ""
},
"opportunity_role": "",
"opportunity_role_fields": "",
"opportunity_role_id": "",
"origine_contact_c": [
"prospection_commerciale"
],
"phone_fax": "",
"phone_home": "",
"phone_mobile": "",
"phone_other": "",
"phone_work": "",
"picture": "",
"portal_active": false,
"portal_app": "",
"portal_name": "",
"portal_password": null,
"portal_password1": null,
"portal_user_company_name": "",
"preferred_language": "",
"primary_address_city": "TEST 2EME",
"primary_address_country": "FRANCE",
"primary_address_postalcode": "99999",
"primary_address_state": "",
"primary_address_street": "43 RUE DE LA TEST",
"primary_address_street_1_c": "43 RUE DE LA TEST",
"primary_address_street_2": "",
"primary_address_street_2_c": "43",
"primary_address_street_3": "",
"primary_address_street_3_c": "",
"primary_address_street_4_c": "",
"profil_linkedin_c": "",
"report_to_name": "",
"reports_to_id": "",
"reports_to_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"salutation": "Mrs.",
"score_engagement_c": "",
"site_user_id": "",
"source_id": "",
"source_meta": "",
"source_type": "",
"statut_c": "",
"statut_hubspot_api_c": "",
"sync_contact": false,
"sync_key": "",
"tag": [],
"team_count": "",
"team_count_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "1",
"team_count": ""
},
"team_name": [
{
"id": "1",
"name": "Global",
"name_2": "",
"primary": true,
"selected": false
}
],
"title": "",
"title_c": "property_manager",
"twitter": ""
},
{
"_acl": {
"fields": {}
},
"_module": "Contacts",
"accept_status_calls": "",
"accept_status_id": "",
"accept_status_meetings": "",
"accept_status_messages": "",
"accept_status_name": "",
"account_id": "617ff1d0-ecf2-11ee-82d8-d05099f915d8",
"account_name": "TEST3",
"accounts": {
"_acl": {
"_hash": "7736b5170dcc226f612bebae1821f44e",
"create": "no",
"delete": "no",
"edit": "no",
"fields": {
"chrono_c": {
"create": "no",
"write": "no"
},
"team_id": {
"create": "no",
"write": "no"
},
"team_name": {
"create": "no",
"write": "no"
}
},
"import": "no",
"massupdate": "no"
},
"id": "617ff1d0-ecf2-11ee-82d8-d05099f915d8",
"name": "TEST3"
},
"action_marketing_c": [],
"alt_address_city": "",
"alt_address_country": "",
"alt_address_postalcode": "",
"alt_address_state": "",
"alt_address_street": "",
"alt_address_street_2": "",
"alt_address_street_3": "",
"assigned_user_id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c",
"assigned_user_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST3 T",
"id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c"
},
"assigned_user_name": "TEST T3",
"assistant": "",
"assistant_phone": "",
"birthdate": "",
"business_center_id": "",
"business_center_name": "",
"business_centers": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"c_accept_status_fields": "",
"calls": {
"id": ""
},
"campaign_contacts": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"campaign_id": "",
"campaign_name": "",
"centre_interets_c": [],
"cookie_consent": false,
"cookie_consent_received_on": "",
"created_by": "cfcff18e-56bc-11e9-885b-0cc47a6d326c",
"created_by_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST T4",
"id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c"
},
"created_by_name": "TEST T5",
"date_entered": "2024-03-28T12:01:49+01:00",
"date_modified": "2024-03-28T12:01:49+01:00",
"decisionnaire_c": "oui",
"deleted": false,
"denorm_account_name": "TEST 4",
"department": "",
"department_c": "",
"description": "",
"desinscription_marketing_c": "",
"dnb_principal_id": "",
"do_not_call": false,
"dp_business_purpose": [],
"dp_consent_last_updated": "2024-03-28",
"dri_workflow_template_id": "",
"dri_workflow_template_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"dri_workflow_template_name": "",
"email": [
{
"email_address": "[email protected]",
"email_address_id": "933504b8-ecf2-11ee-850a-d05099f915d8",
"invalid_email": false,
"opt_out": false,
"primary_address": true,
"reply_to_address": false
}
],
"email1": "[email protected]",
"email2": "",
"email_addresses_non_primary": "",
"email_and_name1": "",
"email_opt_out": false,
"entry_source": "internal",
"facebook": "",
"filiale_c": [
"E0TTT3"
],
"first_name": "TEST3",
"following": false,
"full_name": "TEST TEST5",
"geocode_status": "",
"googleplus": "",
"hint_account_annual_revenue": "",
"hint_account_description": "",
"hint_account_facebook_handle": "",
"hint_account_fiscal_year_end": "",
"hint_account_founded_year": "",
"hint_account_industry": "",
"hint_account_location": "",
"hint_account_logo": "",
"hint_account_naics_code_lbl": "",
"hint_account_sic_code_label": "",
"hint_account_size": "",
"hint_account_twitter_handle": "",
"hint_account_website": "",
"hint_contact_pic": "",
"hint_education": "",
"hint_education_2": "",
"hint_facebook": "",
"hint_industry_tags": "",
"hint_job_2": "",
"hint_phone_1": "",
"hint_phone_2": "",
"hint_photo": "",
"hint_twitter": "",
"id": "933f2768-ecf2-11ee-98bb-d05099f915d8",
"intitule_poste_c": "fondateur",
"invalid_email": false,
"last_name": "TEST",
"lead_source": "",
"locked_fields": [],
"m_accept_status_fields": "",
"mailchimp_rating_c": 2,
"market_interest_prediction_score": "",
"market_score": null,
"meetings": {
"id": ""
},
"mkto_id": null,
"mkto_lead_score": null,
"mkto_sync": false,
"modified_by_name": "TEST TEST2",
"modified_user_id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c",
"modified_user_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"full_name": "TEST TEST 2",
"id": "cfcff18e-56bc-11e9-885b-0cc47a6d326c"
},
"my_favorite": false,
"name": "TEST TEST 5",
"opportunities": {
"id": ""
},
"opportunity_role": "",
"opportunity_role_fields": "",
"opportunity_role_id": "",
"origine_contact_c": [
"prospection_commerciale"
],
"phone_fax": "",
"phone_home": "",
"phone_mobile": "00 00 00 00 99",
"phone_other": "",
"phone_work": "",
"picture": "",
"portal_active": false,
"portal_app": "",
"portal_name": "",
"portal_password": null,
"portal_password1": null,
"portal_user_company_name": "",
"preferred_language": "",
"primary_address_city": "TEST 2EME",
"primary_address_country": "FRANCE",
"primary_address_postalcode": "99999",
"primary_address_state": "",
"primary_address_street": "43 RUE DE LA TEST",
"primary_address_street_1_c": "43 RUE DE LA TEST",
"primary_address_street_2": "",
"primary_address_street_2_c": "43",
"primary_address_street_3": "",
"primary_address_street_3_c": "",
"primary_address_street_4_c": "",
"profil_linkedin_c": "",
"report_to_name": "",
"reports_to_id": "",
"reports_to_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "",
"name": ""
},
"salutation": "Mr.",
"score_engagement_c": "",
"site_user_id": "",
"source_id": "",
"source_meta": "",
"source_type": "",
"statut_c": "",
"statut_hubspot_api_c": "",
"sync_contact": false,
"sync_key": "",
"tag": [],
"team_count": "",
"team_count_link": {
"_acl": {
"_hash": "654d337e0e912edaa00dbb0fb3dc3c17",
"fields": []
},
"id": "1",
"team_count": ""
},
"team_name": [
{
"id": "1",
"name": "Global",
"name_2": "",
"primary": true,
"selected": false
}
],
"title": "",
"title_c": "directeur",
"twitter": ""
}
]','2024-03-29 12:42:02.327');
Edit 2 : for now I only retreive parts of the json with the following query
SELECT
SAC.ORIGINE,
SAC.DATE_CHARGEMENT,
js.VALUE:id::STRING AS ID,
js.VALUE:email1::STRING AS EMAIL,
js.VALUE:description::STRING AS AT_DESCRIPTION,
js.VALUE:salutation::STRING AS AT_SALUTATION,
js.VALUE:first_name::STRING AS AT_PRENOM,
js.VALUE:last_name::STRING AS AT_NOM,
js.VALUE:do_not_call::BOOLEAN AS FG_NE_PAS_APPELER,
js.VALUE:phone_home::STRING AS NUM_TEL_DIRECT,
js.VALUE:phone_mobile::STRING AS NUM_MOBILE,
js.VALUE:phone_work::STRING AS NUM_TEL,
js.VALUE:primary_address_street::STRING AS AT_RUE,
js.VALUE:primary_address_city::STRING AS AT_VILLE,
js.VALUE:primary_address_state::STRING AS AT_REGION,
js.VALUE:primary_address_postalcode::STRING AS CD_POSTAL,
js.VALUE:primary_address_country::STRING AS CD_PAYS,
js.VALUE:lead_source::STRING AS LB_ORIGINE_PRINCIPALE,
js.VALUE:account_id::STRING AS ID_COMPTE,
js.VALUE:market_score::INTEGER AS NB_SCORE_ENGAGEMENT,
js.VALUE:mkto_score_lead::INTEGER AS NB_SCORE_LEAD,
js.VALUE:title_c::STRING AS LB_FONCTION,
js.VALUE:intitule_poste_c::STRING AS LB_POSTE,
js.VALUE:statut_c::STRING AS LB_STATUT,
js.VALUE:decisionnaire_c::STRING AS LB_DECISIONNAIRE,
js.VALUE:departement_c::STRING AS CD_SERVICE,
js.VALUE:statut_hubspot_api_c::STRING AS LB_STATUT_HUBSPOT,
js.VALUE:profil_linkedin_c::STRING AS AT_PROFIL_LINKEDIN,
js.VALUE:action_marketing_c::STRING AS CD_ACTION_MKG,
js.VALUE:desinscription_marketing_c::STRING AS LB_MARKETING,
js.VALUE:deleted::BOOLEAN AS FG_SUPPRIME,
js.VALUE:date_entered::TIMESTAMP_NTZ(9) AS DT_CREATION_CRM,
js.VALUE:date_modified::TIMESTAMP_NTZ(9) AS DT_MODIFICATION_CRM,
FROM DEV_ODS_SOFINORD.ODS.TEST_JSON_VARIANT SAC,
table (flatten (INPUT => SAC.JSON)) js ;
And I wanted to know if this was possible to get all fields of the JSON without having to reference each one in the query
Solution
(thanks for setting up the sample data - I did have to slightly modify the SQL to be able to ingest that sample JSON)
For a JSON object like this, if you want to extract all the data without specifying the name of each column - you will probably need to extract it into rows instead of into columns. This because it's impossible to know how many columns you will need in the case of undetermined length arrays.
To get out all the data, you can use flatten(json, recursive=>true):
select origine, date_chargement, key, path, value, this
from TEST_JSON_VARIANT, table(flatten(json, recursive=>true));
With the sample json I got 546 rows out, for each key in the object. For example, team_name for the 3rd element in an array:
- pyspark statistical window function keeps calculating NULL values
- sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible'
- Sum a record with its previous not-null ordered by timestamp
- Prevent redundant results in SQL query
- SQL Query in Snowflake - Calculate Days of Supply
- How can I find out what FOREIGN KEY constraint references a table in SQL Server?
- How to extract column names from SQL query using Python
- Select on self-referencing table where all chains meet a condition
- SQL query to check if a name begins and ends with a vowel
- In a join, fields with null values are not getting shown but in normal query they are
- MySQL JSON_OBJECT() datetime format
- How to find overlapping records and select the latest record?
- SQL query for returning rows with only certain values in list but not others?
- Why does my query fail to convert a varchar value to int?
- How do I insert a record into a Postgres DB based off the MAX of one column?
- How to auto insert Current DATE in SQL with Java / Hibernate
- IsDate Function in SQL evaluates invalid dates as valid
- SQL Server - Value passes ISDATE() but fails to CAST as DATE or DATETIME
- Snowflake Regex to Camel case file name
- SQL Server DATEDIFF round up the YEAR difference. How to round it down?
- Path table to HTML treeview in SQL Server
- SUM and Decimal precision
- Choosing simple products and variable products separately in WooCommerce tables
- collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"
- Separating fields in the same column
- SQL filter for same values in different columns
- Add multiple conditional values to one column
- How do I repeat a SQL query over a set of records?
- SET CONSTRAINTS ALL DEFERRED not working as expected
- SQL YEAR(GETDATE())