Dynamically lateral flatten in Snowflake

Question

{
  "explosives_UG":{
   "isCritical": false,
   "value": "N/A"
  },
  "explosivesUG": {
   "comment": "Test 619 313",
   "createWo": false,
   "isCritical": false,
   "value": "No"
  },
  "generalAttachment": null,
  "generalComment": "Test 619 313",
  "guardsPostedUG": {
    "isCritical": false,
    "value": "Yes"
  },
  "imminentUG": {
    "isCritical": false,
    "value": "Yes"
  }
}

using the above JSON format, output is generated as below via Snowflake query

question        answer
explosives_UG   N/A
explosivesUG    No
guardsPostedUG  Yes
imminentUG      Yes

SELECT 
  kv.key AS question,
  kv.value:value AS answer
FROM 
  json_data,
  LATERAL FLATTEN(input => json_col) kv
WHERE 
  kv.key IN ('explosives_UG', 'explosivesUG', 'guardsPostedUG', 'imminentUG');

Question : We will be getting lot of questions/answers and handling them manually in the code is time consuming.. How to use dynamically extract JSON values using LATERAL FLATTEN, can somebody help please...

Answer 1

so if we slam that JSON into a single flatten:

with cte_json_data as (
select 
    PARSE_JSON('
    {
  "explosives_UG":{
   "isCritical": false,
   "value": "N/A"
  },
  "explosivesUG": {
   "comment": "Test 619 313",
   "createWo": false,
   "isCritical": false,
   "value": "No"
  },
  "generalAttachment": null,
  "generalComment": "Test 619 313",
  "guardsPostedUG": {
    "isCritical": false,
    "value": "Yes"
  },
  "imminentUG": {
    "isCritical": false,
    "value": "Yes"
  }
}') as json
)
select
    m.key as questions
    ,m.value
from cte_json_data as j
    ,table(flatten(input=>j.json)) as m

we get:

and you want "it appears" the objects that have a known object, thus we can use IS_NULL_VALUE:

with cte_json_data as (
select 
    PARSE_JSON('
    {
  "explosives_UG":{
   "isCritical": false,
   "value": "N/A"
  },
  "explosivesUG": {
   "comment": "Test 619 313",
   "createWo": false,
   "isCritical": false,
   "value": "No"
  },
  "generalAttachment": null,
  "generalComment": "Test 619 313",
  "guardsPostedUG": {
    "isCritical": false,
    "value": "Yes"
  },
  "imminentUG": {
    "isCritical": false,
    "value": "Yes"
  }
}') as json
)
select
    m.key as questions
    ,m.value:value as answer
from cte_json_data as j
    ,table(flatten(input=>j.json)) as m
where is_null_value(m.value:isCritical) = false;

would appear to do what you are after.