1

I have a file named users.json which is 3GB, and is invalid json. So what I'm trying to do is read the file's text content, and take the information that I need, which is the usernames contained in the file, and write them to a usernames.txt file which should contain 1 username per line, with no duplicates.

The format of the usernames in json the file is as follow: "username":"someUsername"

How can I gather all the usernames, put them in the text file and make sure there are no duplicates?

I've tried via Node.js and PHP and nothing as been working efficiently yet, hopefully something cool can be done using bash.

Example of data contained in the file (probably not much help as I already mentioned about the format "username":"someUsername"):

username":"satish_nanded","original_ff_id":"99554"},"100003":{"username":"sweetnamu","original_ff_id":"100003"}},"08fdlhNuZEM1z8q4mQftYUtO7uC3":{"575511":{"username":"lrlgrdnr","original_ff_id":"575511"}},"08fe4Dg7NeOTItq3b9Pi8ORsX5J2":{"59520":{"username":"joneljon","original_ff_id":"59520"}},"08gsZHsbm9Rew4S2IqcbGvD9Fct1":{"724707":{"username":"jacksonc4565","original_ff_id":"724707"}

  • Bash doesn't have any json parsing functionality, much less for a corrupted json file. For any other tools, we'd need an example of the corrupted data to have any hope of extracting useful information. – Jeff Schaller Mar 24 '17 at 19:38
  • Maybe no need JSON parsing per say but text parsing? For example when catching '"username":', take whatever is in the next double quotes (which is username) and write to file. – Dan P. Mar 24 '17 at 19:39
  • Maybe an example of the data you're trying to parse? – roaima Mar 24 '17 at 20:38
  • Updated my post, you can see the format example I gave "username":"someUsername" in action within more data. – Dan P. Mar 24 '17 at 20:43
2

You can use the grep command to match the patterns you need, and sort to filter out duplicates. If your input file is input.json and the output is usernames.txt:

grep -P -o '(?<="username":")[^"]*' input.json | sort -u > usernames.txt

Breaking it down:

  • grep is a command-line utility for matching regular expressions in a file. Regular expressions are a powerful way to describe pieces of text that you wish to find
  • -P tells grep to use "Perl Compatible Regular Expressions". Note that the man page for grep describes this as "highly experimental"!
  • -o tells grep to only output the matching text. By default, grep would normally output the whole line wherever a match is found.
  • '(?<="username":")[^"]*' is the regular expression itself:
    • We put it in single quotes '....' to stop the command-line shell from trying to interpret anything in it
    • (?<=...) is what's called a lookbehind assertion. It says we want to match "username":" before something else, but not include it in the output
    • [^"]* means "as many characters as possible that aren't ". It can be broken down again:
    • [..] is a character class. Any character you put between square brackets is allowed at this point. Unless...
    • ^" When you use a caret ^ as the first character in a character class it means not any of the following characters
    • * means 0 or more of the preceding item (which is the whole of [^"] in this case).

Piping the lot through sort sorts the usernames into alphabetical order, which with the -u option means "unique items only", i.e. no duplicates.

Note: All of this assumes that the pattern we're matching can't occur anywhere else in the file (which seems unlikely), or that the brokenness of the JSON itself won't cause the match to fail (which might be, I'm not sure in what way your file is broken).

EDIT: With grep regularly complaining that the lines were too long, and for some reason sed -e 's/,/,\n/' not really working either, the split command was used to break the file up into more manageable chunks.

  • Very nice, learned a lot from your post, thank you. I'm trying to put it into practice now, at first I got grep ran out of memory, then I upgrade my DigitalOcean server to the biggest one (temporarily), now I get grep: exceeded PCRE's line length limit – Dan P. Mar 24 '17 at 22:02
  • Simple and brilliant. With a 3GB file, I would expect a lot of successive duplicates and I would add a seemingly useless uniq | just before sort -u. That way, hopefully I would relieve sort from a part of its job and make it go faster. – xhienne Mar 24 '17 at 22:02
  • Okay, if it's trying to handle the entire file as a single line, you could try splitting it into multiple lines by add a linebreak after each curly bracket using sed, with sed -e 's/}/}\n/g' input.json | grep -P -o '(?<="username":")[^"]*' | sort -u > usernames.txt – Andrew McCarthy Mar 24 '17 at 22:07
  • hmm, sed -e 's/}/}\n/g' input.json | grep -P -o '(?<="username":")[^"]*' | sort -u > usernames.txt also results in grep: exceeded PCRE's line length limit – Dan P. Mar 24 '17 at 22:10
  • You could try splitting at commas instead of curly brackets (since there's a lot more of them), and maybe use a plain "grep username" to filter out the lines you need before the more complicated grep above, but if that doesn't help it seems that somewhere you do have a long blob of data in your file that you need to work around. I am otherwise out of ideas, I'm afraid! – Andrew McCarthy Mar 24 '17 at 22:18
0

You seem to have very long JSON records that break grep -P, here is an alternate solution:

grep -o '"username":"[^"]*' users.json \
| cut -d '"' -f 4 \
| uniq \
| sort -u \
> usernames.txt

Here, grep extracts the full "username:value" fields, cut extracts the value and uniq | sort -u makes the usernames unique.

uniq is not necessary. With a 3GB file, I expect a list of millions of names, with a lot of successive duplicates. The seemingly useless uniq | relieves sort from a part of its job and may make it go faster. Else, it won't hurt.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.