pets.sql

with dogs as (
  select parse_json($$
  {
    "animals": {
      "dogs": {
          "labrador": {
              "name": "Labrador",
              "weight": 23
          },
          "boxer": {
              "name": "boxer",
              "weight": 55
          },
      },
      "cats": {
          "short hair": {
              "name": "short hair",
              "weight": 10
          },
          "scottish fold": {
              "name": "scottish fold",
              "weight": 6
          },
      },
      "birds": {
          "parrot": {},
          "owl": {
              "name": "owl",
          },
      },
      "fish": {},
    }
  }$$) as dogs
)

select
    level1.key as animal,
    level2.key::string as name_from_label,
    level2.value:name::string as name_from_dict,
    level2.value:weight::int as weight
    
from dogs,
lateral flatten (input => dogs:animals, outer => true) level1,
lateral flatten (input => level1.value, outer => true) level2
order by 1,2,3,4