Pulling Array From JSON using tidyjson

Question

I'm trying pull select arrays from a JSON object using the developer version of the R tidyjson package. I'd like to create the example table below from the example JSON object below. Any help on this would be greatly appreciated.

Here is the table I'm trying to create:

document.id   location.lat  location.lng    viewport     name    place_id
1             32.123451     -85.234541      northeast    Name1   sdfdfasdfasdfdasfdas
1             32.123451     -85.234541      southwest    Name1   sdfdfasdfasdfdasfdas
2             33.345454     -84.345454      northeast    Name2   sdfdsfdsfdff
2             33.345454     -84.345454      southwest    Name2   sdfdsfdsfdff

Here is my JSON object:

    JSON_TEST <- "{
   \"html_attributions\" : [],
\"results\" : [
{
  \"geometry\" : {
  \"location\" : {
  \"lat\" : 32.123451,
  \"lng\" : -85.234541
},
  \"viewport\" : {
  \"northeast\" : {
  \"lat\" : 32.234341,
  \"lng\" : -85.345655
},
  \"southwest\" : {
  \"lat\" : 32.235624,
  \"lng\" : -85.234655
}
}
},
\"icon\" : \"https://fake/fake/fake1.png\",
\"id\" : \"qwerqewrqwerqewrqewrqwreqewrqewrqwr\",
\"name\" : \"Name1\",
\"place_id\" : \"sdfdfasdfasdfdasfdas\",
\"reference\" : \"asdfdasfadsfdasfdfdfdffff\",
\"scope\" : \"TEST1\",
\"types\" : [
\"bar\",
\"liquor_store\",
\"food\",
\"store\",
\"point_of_interest\",
\"establishment\"
],
\"vicinity\" : \"343 Fake Place Lane, Atlanta\"
},
{
  \"geometry\" : {
  \"location\" : {
  \"lat\" : 33.345454,
  \"lng\" : -84.345454
},
  \"viewport\" : {
  \"northeast\" : {
  \"lat\" : 33.234534
  \"lng\" : -84.234643
},
  \"southwest\" : {
  \"lat\" : 33.345443,
  \"lng\" : -84.345422
}
}
},
\"icon\" : \"https://fake/fake/fake2.png\",
\"id\" : \"sdfdsfdsfdff\",
\"name\" : \"Name2\",
\"place_id\" : \"sadfsdfdfdf\",
\"reference\" : \"asdfdasfdsfd\",
\"scope\" : \"TEST2\",
\"types\" : [ \"bar\", \"point_of_interest\", \"establishment\" ],
\"vicinity\" : \"21434 Fake Place Ave, Atlanta\"
}
],
\"status\" : \"OK\"
}
"

Thanks alistaire, I just edited the JSON object and believe I've fixed the escapes and comma. — Bdude11383
– Bdude11383, Commented Jul 19, 2017 at 17:36
As per your other question, you can use my googleway package to query the Google Places API directly — SymbolixAU
– SymbolixAU, Commented Jul 26, 2017 at 1:20

Prem · Accepted Answer · 2017-07-19 18:55:02Z

2

Hope it helps!

JSON_TEST <- 
"{\"html_attributions\" : [],
  \"results\" : [
  {\"geometry\" : {\"location\" : {\"lat\" : 32.123451,\"lng\" : -85.234541},
                   \"viewport\" : {\"northeast\" : {\"lat\" : 32.234341,\"lng\" : -85.345655},
                                   \"southwest\" : {\"lat\" : 32.235624,\"lng\" : -85.234655}
                                  }
                  },
  \"icon\" : \"https://fake/fake/fake1.png\",
  \"id\" : \"qwerqewrqwerqewrqewrqwreqewrqewrqwr\",
  \"name\" : \"Name1\",
  \"place_id\" : \"sdfdfasdfasdfdasfdas\",
  \"reference\" : \"asdfdasfadsfdasfdfdfdffff\",
  \"scope\" : \"TEST1\",
  \"types\" : [\"bar\",\"liquor_store\",\"food\",\"store\",\"point_of_interest\",\"establishment\"],
  \"vicinity\" : \"343 Fake Place Lane, Atlanta\"
  },
  {\"geometry\" : {\"location\" : {\"lat\" : 33.345454,\"lng\" : -84.345454},
                   \"viewport\" : {\"northeast\" : {\"lat\" : 33.234534,\"lng\" : -84.234643},
                                   \"southwest\" : {\"lat\" : 33.345443,\"lng\" : -84.345422}
                                  }
                  },
  \"icon\" : \"https://fake/fake/fake2.png\",
  \"id\" : \"sdfdsfdsfdff\",
  \"name\" : \"Name2\",
  \"place_id\" : \"sadfsdfdfdf\",
  \"reference\" : \"asdfdasfdsfd\",
  \"scope\" : \"TEST2\",
  \"types\" : [ \"bar\", \"point_of_interest\", \"establishment\" ],
  \"vicinity\" : \"21434 Fake Place Ave, Atlanta\"
  }
  ],
\"status\" : \"OK\"
}"

#devtools::install_github("sailthru/tidyjson")
library(tidyjson)
library(dplyr)
JSON_TEST <- gsub("\\n","",JSON_TEST)
JSON_TEST %>%        
  as.tbl_json %>% 
  enter_object("results") %>%
  gather_array %>%  
  spread_values(    
      name = jstring("name"),
      place_id = jstring("place_id")  
  ) %>%
  enter_object("geometry") %>%
  spread_values(    
    location.lat = jnumber("location","lat"),
    location.lng = jnumber("location","lng")
    ) %>%  
  enter_object("viewport") %>%
  gather_keys("viewport")

Don't forget to let us know if it solved your problem :)

answered Jul 19, 2017 at 18:55

Prem

12k1 gold badge21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bdude11383 Over a year ago

so close! Did you modify my original JSON_TEST object in some way?. Your code works perfectly on the JSON_TEST object embedded in your answer, but I get the following error when I run on the original JSON_TEST: "Error: parse error: after key and value, inside map, I expect ',' or '}' st" : { "lat" : 33.234534 "lng" : -84.234643}, "southwest (right here) ------^"

Prem Over a year ago

Yes, one comma is missing after \"viewport\" : {\"northeast\" : {\"lat\" : 33.234534. BTW - If it solved your problem then your should accept (or vote) it as the right answer :)

SymbolixAU · Accepted Answer · 2017-07-26 01:25:37Z

Your data looks like it comes from Google's Places API. Therefore, you can by-pass the JSON formatting and use my googleway package to get the results for you

library(googleway)

api_key <- 'your_api_key'

myPlaces <- google_places(search_string = "Restaurants in Melbourne",
                        key = api_key)

the results come back as a list in R, so you can grab the pieces of information directly

head(cbind(myPlaces$results$geometry, myPlaces$results$place_id))

# location.lat location.lng viewport.northeast.lat viewport.northeast.lng viewport.southwest.lat viewport.southwest.lng
# 1     28.07650    -80.59910               28.07842              -80.59773               28.07572              -80.60043
# 2     28.07724    -80.60456               28.07843              -80.60320               28.07573              -80.60590
# 3     28.07872    -80.60723               28.07993              -80.60588               28.07723              -80.60858
# 4     28.07950    -80.60212               28.08073              -80.60069               28.07803              -80.60339
# 5     28.21043    -80.66411               28.21174              -80.66287               28.20904              -80.66557
# 6     28.07839    -80.60321               28.07982              -80.60167               28.07712              -80.60437
# myPlaces$results$place_id
# 1 ChIJSzCXdo8R3ogRodiPcpYYLGw
# 2 ChIJl_1IpI4R3ogR50nk7fYHdb8
# 3 ChIJ2QdwWowR3ogRKJOrSqPQuYU
# 4 ChIJK_gOU44R3ogRGXv8ScI7-t0
# 5 ChIJxytL4RwF3ogRw9qGq8mSm5w
# 6 ChIJa7H_5I4R3ogRys0um892_VA

Collectives™ on Stack Overflow

Pulling Array From JSON using tidyjson

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related