0

I have nested JSON (lines) file which looks like

[{"ok":2, "nested": {"meh":2, "hehe":{"a":1, "b":2 }}},
{"ok":3, "nested": {"meh":10, "hehe":{"a":1, "b":2}}},
{"ok":4, "nested": {"meh":11, "hehe":{"a":1, "b":2}}}]
...

and I wish to unnest it into the data.frame represented CSV form

ok, nested.meh, nested.hehe.a, nested.hehe.b
2, 2, 1, 2
3, 10, 1, 2
4, 11, 1, 2
...

I tried 

a = jsonlite::read_json("file.json")

But then a list of list, which seems to need custom coding to unnest, but I want to read general nested files. Is there a package to handle this? I looked through the documentation for rlist but couldn't find info as rlist::table didn't work.

xiaodai
  • 14,889
  • 18
  • 76
  • 140
  • check out: https://stackoverflow.com/questions/35444968/read-json-file-into-a-data-frame-without-nested-lists – MatthewR Mar 05 '20 at 02:21

2 Answers2

0

This works for me:

a = jsonlite::fromJSON("file.json", flatten = TRUE)

I created file.json by pasting the below into a blank Notepad++ text document. Sometimes my issues importing json files into R have been because of text formatting issues, so also double check original file for errant whitespace or other characters.

Data

[{"ok":2, "nested": {"meh":2, "hehe":{"a":1, "b":2 }}},
{"ok":3, "nested": {"meh":10, "hehe":{"a":1, "b":2}}},
{"ok":4, "nested": {"meh":11, "hehe":{"a":1, "b":2}}}]
Jim Kloet
  • 440
  • 3
  • 7
  • 2
    Have you called `str(a)`? What I'm getting *looks* like a flat data frame, but a little investigation shows it's actually the type of nested list the OP is describing – camille Mar 05 '20 at 02:47
  • oops, forgot to add `flatten = TRUE`, editing – Jim Kloet Mar 05 '20 at 02:49
0

I usually go for the flatten_json package

import flatten_json

ex_json = [{"ok":2, "nested": {"meh":2, "hehe":{"a":1, "b":2 }}},
{"ok":3, "nested": {"meh":10, "hehe":{"a":1, "b":2}}},
{"ok":4, "nested": {"meh":11, "hehe":{"a":1, "b":2}}}]

ex_df = pd.io.json.json_normalize(flatten_json.flatten_json(ex_json[0], separator="."))
lemonlin
  • 96
  • 6