2

I have roughly 25,000 JSON files that I need to validate and check and I have noticed that some of the files have duplicated themselves causing errors when converting the JSON into a CSV using Excel.

I am trying to delete all string after "version":"0.2.3"} as this indicates the end of the JSON before repeating itself "version": "0.2.3"}{"analysis": { so I need to keep "version": "0.2.3"} and remove {"analysis": { and everything that follows it and apply these changes to all 25,000 files. I have seen people using grep and sed but can't seem to get them working myself.

Would appreciate if anyone can help as manually deleting the text isn't advisable so I am hoping for a one liner in bash or something!

Below is what I need to fix (trimmed down version of one of the files) and below that is the desired output. As you can see for whatever reason the JSON data duplicated itself maybe during the API usage.

Problematic Input (trimmed json):

{"analysis":{"score":3},"sample":{"completed":"2022-01-27T21:22:21Z","created":"2022-01-27T21:17:57Z","id":"220127-z5h84saffl","md5":"7871a75734af389b787bad57a3ea087d","score":3,"sha1":"58a8689ee76a46559ea56a52d20425f44c8ff601","sha256":"23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a"},"version":"0.2.3"}{"analysis":{"score":3},"sample":{"completed":"2022-01-27T21:22:21Z","created":"2022-01-27T21:17:57Z","id":"220127-z5h84saffl","md5":"7871a75734af389b787bad57a3ea087d","score":3,"sha1":"58a8689ee76a46559ea56a52d20425f44c8ff601","sha256":"23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a"},"version":"0.2.3"}

Desired Output:

{"analysis":{"score":3},"sample":{"completed":"2022-01-27T21:22:21Z","created":"2022-01-27T21:17:57Z","id":"220127-z5h84saffl","md5":"7871a75734af389b787bad57a3ea087d","score":3,"sha1":"58a8689ee76a46559ea56a52d20425f44c8ff601","sha256":"23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a"},"version":"0.2.3"}
3
  • 2
    It seems like the data that you're dealing with is JSON. Specific utilities are available for working with JSON data that would make this operation trivial. Still, to be able to suggest anything that would be useful to you, you would first have to show a complete example of one of the JSON documents. Also, converting JSON into CSV is easily done with command-line tools.
    – Kusalananda
    Jun 19, 2022 at 12:25
  • If you have clarifications to your question, please edit the text of the question.
    – Kusalananda
    Jun 19, 2022 at 12:28
  • 1
    @Kusalananda I have added two samples which I hope can provide some additional context! I am looking for a way to remove everything after the first instance of "version":"0.2.3"}
    – Jarvis4444
    Jun 19, 2022 at 12:34

2 Answers 2

5

Using jq (available for most Unix-like systems), you could extract the first JSON object from a file and discard the rest using

jq -n 'input' file >newfile

This produces "pretty-printed" JSON. Use the -c option to get "compact" output (a single line with minimal whitespace). The output is written to newfile.

The jq instruction input produces the next available JSON object. We turn off the default reading of the data using -n (--null-input) and use input to only process the very first object in the input file.

For the given data in the question, this produces

{
  "analysis": {
    "score": 3
  },
  "sample": {
    "completed": "2022-01-27T21:22:21Z",
    "created": "2022-01-27T21:17:57Z",
    "id": "220127-z5h84saffl",
    "md5": "7871a75734af389b787bad57a3ea087d",
    "score": 3,
    "sha1": "58a8689ee76a46559ea56a52d20425f44c8ff601",
    "sha256": "23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a"
  },
  "version": "0.2.3"
}

Then simple overwrite the original file with the result. The complete processing of a single file could possibly look like

jq -n 'input' file >newfile &&
mv newfile file

Assuming your files are located in the current directory and match some pattern *.json, then you may process all files in a simple shell loop like this:

for name in *.json; do
    cp -- "$name" "$name.orig" &&
    jq -n 'input' <"$name.orig" >"$name"
done

This does things a bit differently from what I just showed, leaving the original contents in files with a .orig filename suffix and ensuring that the original file's metadata (permissions etc.) are not altered. Once you have made sure this does the correct thing, you may delete the files with the .orig filename suffix.

Always test things like this on data that is properly backed up.


You also mentioned converting these files into CSV but said nothing else about what you needed regarding this. Assuming you might want the sample data as CSV, with the keys as headers:

jq -n -r 'input | .sample | keys, [.[]] | @csv' file

The -r option (--raw-output) is needed to give you decoded data, not JSON strings.

For the given document, this would give you

"completed","created","id","md5","score","sha1","sha256"
"2022-01-27T21:22:21Z","2022-01-27T21:17:57Z","220127-z5h84saffl","7871a75734af389b787bad57a3ea087d",3,"58a8689ee76a46559ea56a52d20425f44c8ff601","23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a"
3
  • This worked brilliant for a single file! I also appreciate the help with converting it to a CSV file. do you have any advice for doing this to 28,678 files in a directory? I will try turning it into a loop and see where it gets me
    – Jarvis4444
    Jun 19, 2022 at 12:48
  • @Jarvis4444 See added bit before the divider.
    – Kusalananda
    Jun 19, 2022 at 12:52
  • 1
    @Jarvis4444 For more precise help with converting into CSV, I would suggest asking a separate question about it. We tend to want single-issue questions.
    – Kusalananda
    Jun 19, 2022 at 12:56
0

Using Raku (formerly known as Perl_6)

Sample Input (erroneous JSON):

~$ cat file.json
{"analysis":{"score":3},"sample":{"completed":"2022-01-27T21:22:21Z","created":"2022-01-27T21:17:57Z","id":"220127-z5h84saffl","md5":"7871a75734af389b787bad57a3ea087d","score":3,"sha1":"58a8689ee76a46559ea56a52d20425f44c8ff601","sha256":"23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a"},"version":"0.2.3"}{"analysis":{"score":3},"sample":{"completed":"2022-01-27T21:22:21Z","created":"2022-01-27T21:17:57Z","id":"220127-z5h84saffl","md5":"7871a75734af389b787bad57a3ea087d","score":3,"sha1":"58a8689ee76a46559ea56a52d20425f44c8ff601","sha256":"23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a"},"version":"0.2.3"}

Sample Output (Raku's hash format):

~$ cat file.json | raku -MJSON::Stream -e 'my @a;  \
   react whenever json-stream($*IN.Supply, [q[$], *][0],) {@a.push($_.values)};  \
   "\n".print; .say for @a.[0].hash;'

sample => {completed => 2022-01-27T21:22:21Z, created => 2022-01-27T21:17:57Z, id => 220127-z5h84saffl, md5 => 7871a75734af389b787bad57a3ea087d, score => 3, sha1 => 58a8689ee76a46559ea56a52d20425f44c8ff601, sha256 => 23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a}
version => 0.2.3
analysis => {score => 3}

Above, incomplete JSON can be read-in using Raku's JSON::Stream module, which--because it's a JSON stream parser (parsing before the EOF is reached)--doesn't validate the JSON. That's fortunate in this case. The erroneous second element gets dropped from the array using the @a.[0].hash call (@a.head.hash also works).


Final Output in JSON format is accomplished using the to-json() command from Raku's JSON::Tiny module. Putting it all together, this is what we do:

Final JSON Output (using only Raku modules):

~$ cat Jarvis4444.json | raku -MJSON::Stream -MJSON::Tiny -e 'my @a;  \
   react whenever json-stream($*IN.Supply, [q[$], *][0],) {@a.push($_.values)};  \
   "\n".print; to-json(@a.[0].hash).put;'

{ "version" : "0.2.3", "sample" : { "score" : 3, "created" : "2022-01-27T21:17:57Z", "sha1" : "58a8689ee76a46559ea56a52d20425f44c8ff601", "id" : "220127-z5h84saffl", "sha256" : "23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a", "md5" : "7871a75734af389b787bad57a3ea087d", "completed" : "2022-01-27T21:22:21Z" }, "analysis" : { "score" : 3 } }

For comparison with other answers, the JSON output immediately above can be | piped through jq:

{
  "sample": {
    "completed": "2022-01-27T21:22:21Z",
    "score": 3,
    "sha1": "58a8689ee76a46559ea56a52d20425f44c8ff601",
    "md5": "7871a75734af389b787bad57a3ea087d",
    "id": "220127-z5h84saffl",
    "sha256": "23c5dee027c7969aabb5828641c55a005f30fb166a5006dbe3a817f56ca0e32a",
    "created": "2022-01-27T21:17:57Z"
  },
  "version": "0.2.3",
  "analysis": {
    "score": 3
  }
}

[Processing multiple files: Certainly the answer by @Kusalananda provides ample guidance on writing a shell loop to process multiple files, correcting/saving each one].

https://github.com/moritz/json
https://github.com/FCO/JSON-Stream
https://raku.org

You must log in to answer this question.

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