How to flatten Json data which having high number of child values

Question

I need to flatten json format data into structured table format. json data in this format. It has more than 100 countries. I tried several ways but could not found the solution.

{
   "Country":{
      "0":"USA",
      "1":"Japan",
      "2":"Canada",
      "3":"Mexico"
   },
   "Capital":{
      "0":"Washington",
      "1":"Tokyo",
      "2":"Ottawa",
      "3":"Mexico City"
   },
   "Currency":{
      "0":"USD",
      "1":"Yen",
      "2":"Canadian Dollar",
      "3":"Peso"
   }
}

to,

 Id| country | Capital    | Currency|
 -----------------------------------
  0| USA     | Washington | USD     |
  1| Japan   | Tokyo      | Yen     |

etc.

Answer 1

To flatten while iterating over 3 arrays at the same time, make sure to match each lateral flatten() key when unnesting them:

with data as (select parse_json('{
   "Country":{
      "0":"USA",
      "1":"Japan",
      "2":"Canada",
      "3":"Mexico"
   },
   "Capital":{
      "0":"Washington",
      "1":"Tokyo",
      "2":"Ottawa",
      "3":"Mexico City"
   },
   "Currency":{
      "0":"USD",
      "1":"Yen",
      "2":"Canadian Dollar",
      "3":"Peso"
   }
}') v)

select country.value::varchar country
  , capital.value::varchar capital
  , currency.value::varchar currency
from data
  , lateral flatten(input => v:Country) country
  , lateral flatten(input => v:Capital) capital
  , lateral flatten(input => v:Currency) currency
where country.key=capital.key
and country.key=currency.key