We have the following newline-delimited JSON:

{"leagueId": "1", "name": "the ballers"}
{"team": "2", "leagueId": "1", "name": "the hoopers"}
{"team": "3", "leagueId": "1", "name": "the gamerrs"}
{"team": "4", "leagueId": "1", "name": "the drivers"}
{"team": "5", "leagueId": "1", "name": "the jumpers"}
{"team": "6", "leagueId": "1", "name": "the riserss"}

team, leagueId should both be ints, and we'd like to modify this NDJSON converting these strings into ints. The output we're going for is:

{"leagueId": 1, "name": "the ballers"}
{"team": 2, "leagueId": 1, "name": "the hoopers"}
{"team": 3, "leagueId": 1, "name": "the gamerrs"}
{"team": 4, "leagueId": 1, "name": "the drivers"}
{"team": 5, "leagueId": 1, "name": "the jumpers"}
{"team": 6, "leagueId": 1, "name": "the riserss"}

Assuming we know / have a list/array of the columns that need to be converted from strings into ints [team, leagueId], how can we do this conversion? Is this possible with (a) a bash command using a tool like jq, or (b) is there some python solution? Our full NDJSON is ~10GB in size, and performance is important as this is a step in our daily data-ingestion pipeline.

Edit: jq -c '{leagueId: .leagueId | tonumber, team: .team | tonumber, name: .name}' tmp/testNDJSON.json > tmp/new_output.json it seems like this this would work if not for the missing team value in the first JSON... any help is appreciated!

  • 1
    Why do you use a bunch of JSON objects instead of a single JSON object with arrays? Doesn't really make sense IMHO.
    – svin83
    Aug 4, 2021 at 22:30
  • We use newline-delimited JSON, it is the format outputted to us when we export from our database.
    – Canovice
    Aug 4, 2021 at 22:30
  • 1
    Can't those field types simply be changed from string to integer directly in your database client? Shouldn't be a problem.
    – svin83
    Aug 4, 2021 at 22:41
  • our database is mongodb, and yes we actually have an okay working solution with pymongo's collection.update_many() that updates types directly in mongo. however (a) it is fairly slow and we're searching for a faster solution.
    – Canovice
    Aug 4, 2021 at 22:44

1 Answer 1

jq  -c '.leagueId |= tonumber | (.team // empty) |= tonumber' file

This converts the leagueId in each object to a number, and then the team value, if it exits and is non-null.

Generalized to take an array of keys to turn into numbers, if they exist:

jq -c --argjson keys '["team", "leagueId"]' \
    '($keys[] as $key | .[$key] // empty) |= tonumber' file

Using jo to create that list:

jq -c --argjson keys "$(jo -a team leagueId)"  \
    '($keys[] as $key | .[$key] // empty) |= tonumber' file

What happens here is that the statement .[$key] // empty is used for a set of $key strings, generating values from the objects where $key is an existing key with non-null value. These values are then converted to numbers. The $key strings are taken from the array passed in on the command line.

  • In our full data, we know which keys need to be converted, but we don't know which keys have / don't have missing values. We are looking for a solution that assumes any of the keys may have missing values.
    – Canovice
    Aug 4, 2021 at 22:45
  • this solution still provides us enough that i'll accept this answer. thank you so much!
    – Canovice
    Aug 4, 2021 at 22:46
  • 1
    @Canovice See updated answer.
    – Kusalananda
    Aug 4, 2021 at 22:54
  • I wish I could upvote this more than once
    – Canovice
    Aug 4, 2021 at 23:15
  • quick follow up on this - while this command works locally with jq version 1.6 on mac, it is not working in our docker container with jq version jq-1.5-1-a5b5cbe jq: error (at /path_to/file.json:1): Invalid path expression near attempt to iterate through ["team","leagueId"]. This error message prints once for each line in the file, so we also get jq: error (at /path_to/file.json:2): Invalid path expression near attempt to iterate through ["team","leagueId"]
    – Canovice
    Aug 9, 2021 at 19:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .