How to create a Dataset from Nested JSON Data?

Question

I'm importing data from Swarm into Mathematica, and I'm trying to create a dataset and having issues because the values are at different levels:

$.venue.name
$.venue.location.address
$.venue.location.city
$.venue.location.state
$.venue.location.postalCode
$.venue.location.lat
$.venue.location.lng

I have this:

checkinData[All, "venue", "location", {"address", "city", "state", "postalCode", "lat", "lng"}]

which provides everything but that first column that's at a higher level. How do I get that higher-level column in?

Answer 1

Most of the functionality you seek can be found in the Wolfram Function Repository functions ResourceFunction["ToAssociations"] and ResourceFunction["AssociationKeyFlatten"].

With

json=ImportString[
 "{
   \"Venue\":{\"Name\":\"ABC\",\"Location\":{\"L1\":\"DEF\",\"L2\":\"GHI\"}}
   ,\"Venue\":{\"Name\":\"JKL\",\"Location\":{\"L1\":\"MNO\",\"L2\":\"PQR\"}}
  }"
 ,"JSON"
 ];

Then

flatJson =
 Map[
  KeyMap[StringRiffle[#, "."] &]
  , Map[
   ResourceFunction["AssociationKeyFlatten"]
   , ResourceFunction["ToAssociations"]@Map[List, json]
   ]
  ]

{<|"Venue.Name" -> "ABC", "Venue.Location.L1" -> "DEF", "Venue.Location.L2" -> "GHI"|>
 , <|"Venue.Name" -> "JKL", "Venue.Location.L1" -> "MNO", "Venue.Location.L2" -> "PQR"|>}

and

Dataset[flatJson]

Hope this helps.

Answer 2

Not pretty, but I got it to work by creating a new Table with the required data points:

url = StringTemplate[
    "https://api.foursquare.com/v2/users/self/checkins?afterTimestamp=\
`a`&oauth_token=`b`&limit=250&v=20220406"][<|"a" -> startTime, 
    "b" -> oauthToken|>];
checkins = 
  Import[url, "RawJSON"][["response"]][["checkins"]][["items"]];
venues = 
  Table[Association["name" -> checkins[[i]][["venue"]][["name"]], 
    "lat" -> 
     GeoPosition[{checkins[[i]][["venue"]][["location"]][["lat"]], 
       checkins[[i]][["venue"]][["location"]][["lng"]]}]], {i, 
    Length[checkins]}];