JSON / JSONL

Files ending in .json are read with jsonlite::fromJSON; .jsonl and .ndjson files are read line-by-line with jsonlite::stream_in. Write JSON output with -O json or by naming the output file .json.

Reading JSON

rush run -O json 'data.frame(name = c("Alice", "Bob", "Carol"), age = c(30, 25, 35), city = c("Amsterdam", "Rotterdam", "Utrecht"))' > people.json
cat people.json
#> [
#>   {
#>     "name": "Alice",
#>     "age": 30,
#>     "city": "Amsterdam"
#>   },
#>   {
#>     "name": "Bob",
#>     "age": 25,
#>     "city": "Rotterdam"
#>   },
#>   {
#>     "name": "Carol",
#>     "age": 35,
#>     "city": "Utrecht"
#>   }
#> ]

rush run 'df |> dplyr::filter(age > 28)' people.json
#> name,age,city
#> Alice,30,Amsterdam
#> Carol,35,Utrecht

Writing JSON

Use -O json or name the output file .json:

rush run -O json 'head(df, 3)' penguins.csv
#> [
#>   {
#>     "species": "Adelie",
#>     "island": "Torgersen",
#>     "bill_length_mm": 39.1,
#>     "bill_depth_mm": 18.7,
#>     "flipper_length_mm": 181,
#>     "body_mass_g": 3750,
#>     "sex": "male",
#>     "year": 2007
#>   },
#>   {
#>     "species": "Adelie",
#>     "island": "Torgersen",
#>     "bill_length_mm": 39.5,
#>     "bill_depth_mm": 17.4,
#>     "flipper_length_mm": 186,
#>     "body_mass_g": 3800,
#>     "sex": "female",
#>     "year": 2007
#>   },
#>   {
#>     "species": "Adelie",
#>     "island": "Torgersen",
#>     "bill_length_mm": 40.3,
#>     "bill_depth_mm": 18,
#>     "flipper_length_mm": 195,
#>     "body_mass_g": 3250,
#>     "sex": "female",
#>     "year": 2007
#>   }
#> ]

rush run -o top3.json 'head(df, 3)' penguins.csv
cat top3.json
#> [
#>   {
#>     "species": "Adelie",
#>     "island": "Torgersen",
#>     "bill_length_mm": 39.1,
#>     "bill_depth_mm": 18.7,
#>     "flipper_length_mm": 181,
#>     "body_mass_g": 3750,
#>     "sex": "male",
#>     "year": 2007
#>   },
#>   {
#>     "species": "Adelie",
#>     "island": "Torgersen",
#>     "bill_length_mm": 39.5,
#>     "bill_depth_mm": 17.4,
#>     "flipper_length_mm": 186,
#>     "body_mass_g": 3800,
#>     "sex": "female",
#>     "year": 2007
#>   },
#>   {
#>     "species": "Adelie",
#>     "island": "Torgersen",
#>     "bill_length_mm": 40.3,
#>     "bill_depth_mm": 18,
#>     "flipper_length_mm": 195,
#>     "body_mass_g": 3250,
#>     "sex": "female",
#>     "year": 2007
#>   }
#> ]

Reading JSONL

Files ending in .jsonl or .ndjson are read line-by-line:

rush run -O jsonl 'data.frame(event = c("login", "purchase", "logout"), user = c("alice", "bob", "alice"), ts = 1:3)' > events.jsonl
cat events.jsonl
#> {"event":"login","user":"alice","ts":1}
#> {"event":"purchase","user":"bob","ts":2}
#> {"event":"logout","user":"alice","ts":3}

rush run 'nrow(df)' events.jsonl
#> 3

rush run 'df |> dplyr::filter(user == "alice")' events.jsonl
#> event,user,ts
#> login,alice,1
#> logout,alice,3

Writing JSONL

Streaming one JSON object per line, great for log pipelines:

rush run -O jsonl 'head(df, 3)' penguins.csv
#> {"species":"Adelie","island":"Torgersen","bill_length_mm":39.1,"bill_depth_mm":18.7,"flipper_length_mm":181,"body_mass_g":3750,"sex":"male","year":2007}
#> {"species":"Adelie","island":"Torgersen","bill_length_mm":39.5,"bill_depth_mm":17.4,"flipper_length_mm":186,"body_mass_g":3800,"sex":"female","year":2007}
#> {"species":"Adelie","island":"Torgersen","bill_length_mm":40.3,"bill_depth_mm":18,"flipper_length_mm":195,"body_mass_g":3250,"sex":"female","year":2007}

Nested JSON and flattening

When outputting to CSV/TSV or any format that does not support nesting, nested JSON objects are automatically flattened using jsonlite::flatten(). Nested field names become dot-separated (then normalized to underscores by clean_names):

printf '{"user":"alice","event":"login","meta":{"ip":"192.168.1.1","browser":"firefox"}}\n{"user":"bob","event":"purchase","meta":{"ip":"10.0.0.5","browser":"chrome"}}\n{"user":"carol","event":"logout","meta":{"ip":"172.16.0.1","browser":"safari"}}\n' > logs.jsonl
rush run 'df' logs.jsonl
#> user,event,meta_ip,meta_browser
#> alice,login,192.168.1.1,firefox
#> bob,purchase,10.0.0.5,chrome
#> carol,logout,172.16.0.1,safari

Deeper nesting is flattened recursively:

printf '{"id":1,"info":{"name":"Widget","dims":{"w":10,"h":5,"d":3}}}\n{"id":2,"info":{"name":"Gadget","dims":{"w":8,"h":4,"d":2}}}\n' > products.jsonl
rush run 'df' products.jsonl
#> id,info_name,info_dims_w,info_dims_h,info_dims_d
#> 1,Widget,10,5,3
#> 2,Gadget,8,4,2

When outputting to JSON, nesting is preserved as-is (no flattening occurs):

rush run -O json 'df' logs.jsonl
#> [
#>   {
#>     "user": "alice",
#>     "event": "login",
#>     "meta": {
#>       "ip": "192.168.1.1",
#>       "browser": "firefox"
#>     }
#>   },
#>   {
#>     "user": "bob",
#>     "event": "purchase",
#>     "meta": {
#>       "ip": "10.0.0.5",
#>       "browser": "chrome"
#>     }
#>   },
#>   {
#>     "user": "carol",
#>     "event": "logout",
#>     "meta": {
#>       "ip": "172.16.0.1",
#>       "browser": "safari"
#>     }
#>   }
#> ]

This also works with rush convert:

rush convert -o logs.csv logs.jsonl
cat logs.csv
#> user,event,meta_ip,meta_browser
#> alice,login,192.168.1.1,firefox
#> bob,purchase,10.0.0.5,chrome
#> carol,logout,172.16.0.1,safari

JSON with SQL

DuckDB reads JSON/JSONL with read_json_auto:

rush sql "SELECT name, age FROM people WHERE age >= 30" people.json
#> name,age
#> Alice,30
#> Carol,35

rush sql "SELECT event, user FROM events WHERE user = 'alice'" events.jsonl
#> event,user
#> login,alice
#> logout,alice

Using `--input-format` with JSON

Read a file with a non-standard extension as JSON:

cp people.json people.dat
rush run -F json 'head(df)' people.dat
rm -f people.dat
#> name,age,city
#> Alice,30,Amsterdam
#> Bob,25,Rotterdam
#> Carol,35,Utrecht