1

I have a very big jsonl file (several million lines).
I want to sort this file on a given value, but I don't want to load it entirely in RAM.
Would you have a solution to suggest ?

I had a look at jq with a sort_by option, but I think the file is not streamed.

Extra note :

  • The order among a group does not matter
  • Having as many outputs as username is also good to me, if the method requires splitting the file.

Example :

Here is a dummy example of what my input file looks like :

{"username": "user1", "email": "email1", "value": "10"}
{"username": "user2", "email": "email2", "value": "30"}
{"username": "user2", "email": "email2", "value": "30"}
{"username": "user1", "email": "email1", "value": "5"}
{"username": "user3", "email": "email3", "value": "15"}
{"username": "user1", "email": "email1", "value": "40"}
{"username": "user3", "email": "email1", "value": "40"}

Here is the output I would like :

{"username": "user1", "email": "email1", "value": "10"}
{"username": "user1", "email": "email1", "value": "5"}
{"username": "user1", "email": "email1", "value": "40"}
{"username": "user2", "email": "email2", "value": "30"}
{"username": "user2", "email": "email2", "value": "30"}
{"username": "user3", "email": "email3", "value": "15"}
{"username": "user3", "email": "email1", "value": "40"}
plalanne
  • 1,010
  • 2
  • 13
  • 30

1 Answers1

2

One approach is to transform the document to end up with lines that can be sorted by a tool that handles limited memory, such as the sort unix command-line utility.

You can use the following:

jq -r '"\( .username )\u0000\( tojson )"' a.json |
sort |
jq -Rc '. / "\u0000" | .[-1] | fromjson'

For the provided input, the above produces the following output:

{"username":"user1","email":"email1","value":"10"}
{"username":"user1","email":"email1","value":"40"}
{"username":"user1","email":"email1","value":"5"}
{"username":"user2","email":"email2","value":"30"}
{"username":"user2","email":"email2","value":"30"}
{"username":"user3","email":"email1","value":"40"}
{"username":"user3","email":"email3","value":"15"}

Along the same lines, you could produce a TSV (jq -r '"\( .username )\t\( tojson )"') you could inject into a database. Then it's a simple SQL query to extract the sorted JSON documents.

ikegami
  • 367,544
  • 15
  • 269
  • 518
  • Thanks a lot @ikegami, for the record I add a [link to this question](https://stackoverflow.com/questions/23697241/how-does-sort-work-out-how-much-ram-there-is) that is interesting to understand how `sort` sets the RAM limit. – plalanne Mar 15 '22 at 08:27