Extract data in columnar format from JSON in R

Question

I want to extract data from the json object in R
R Package used tidyjson, magrittr, jsonlite

trial <- '[{ "KEYS": {"USER_ID": "1266", "MOBILE_NO": "9000000000"}}]'

trial %>%  
  gather_array %>% # stack as an array
  spread_values(USER_ID = jstring("KEYS.USER_ID"), 
MOBILE_NO = jstring("KEYs.MOBILE_NO")  )

Output of this code is not as required. Anyone with suggestions.

 document.id array.index USER_ID MOBILE_NO
1           1           1    <NA>      <NA>

Expected output:

 document.id array.index USER_ID MOBILE_NO
         1           1    1266     9000000000

if `trial` really is an array with many objects, adding a few more into the example along with the `library()` calls for the pkgs you used would enable folks to help you better. — hrbrmstr, Oct 16 '16 at 10:22
Similar to example here http://stackoverflow.com/questions/16947643/getting-imported-json-data-into-a-data-frame-in-r — Sun Bee, Oct 16 '16 at 11:16

score 0 · Answer 1 · answered May 14 '17 at 04:45

tidyjson uses multi-parameter paths, rather than "dot-separated" paths, as you attempted. You can really tackle this two ways:

Recommended, as it does not throw away the rest of the object:

trial <- '[{ "KEYS": {"USER_ID": "1266", "MOBILE_NO": "9000000000"}}]'

trial %>%  
    gather_array %>% # stack as an array
    spread_values(USER_ID = jstring('KEYS','USER_ID'), 
                  MOBILE_NO = jstring('KEYS','MOBILE_NO'))

Can also use enter_object if preferred or necessary:

trial <- '[{ "KEYS": {"USER_ID": "1266", "MOBILE_NO": "9000000000"}}]'

trial %>%  
  gather_array %>% # stack as an array
  enter_object('KEYS') %>%
  spread_values(USER_ID = jstring('USER_ID'), 
                MOBILE_NO = jstring('MOBILE_NO'))

Extract data in columnar format from JSON in R

1 Answers1