1

I have a newline delimited JSON file with entries like this:

{"id":"eprints.ulster.ac.uk/view/year/2015.html","title":"Items where Year is 2015 - Ulster Institutional Repository","url":"eprints.ulster.ac.uk/view/year/2015.html"}
{"id":"eprints.ulster.ac.uk/view/year/2016.html","title":"Items where Year is 2016 - Ulster Institutional Repository","url":"eprints.ulster.ac.uk/view/year/2016.html"}
{"id":"eprints.ulster.ac.uk/view/year/2017.html","title":"Items where Year is 2017 - Ulster Institutional Repository","url":"eprints.ulster.ac.uk/view/year/2017.html"}
{"id":"eprints.ulster.ac.uk/10386/","title":"Structural performance of rotationally restrained steel columns in fire - Ulster Institutional Repos","url":"eprints.ulster.ac.uk/10386/"}
{"id":"eprints.ulster.ac.uk/10387/","title":"Determining the Effective Length of Fixed End Steel Columns in Fire - Ulster Institutional Repositor","url":"eprints.ulster.ac.uk/10387/"}

I only want blocks where the .id does not begin with "eprints.ulster.ac.uk/view/"

So if the script was run on the above code snippet, the first 3 blocks would be deleted and the only blocks remaining would be:

{"id":"eprints.ulster.ac.uk/10386/","title":"Structural performance of rotationally restrained steel columns in fire - Ulster Institutional Repos","url":"eprints.ulster.ac.uk/10386/"}
{"id":"eprints.ulster.ac.uk/10387/","title":"Determining the Effective Length of Fixed End Steel Columns in Fire - Ulster Institutional Repositor","url":"eprints.ulster.ac.uk/10387/"}

Can anybody help write an awk script to do this?

0

Seeing as you specifically asked for an Awk solution:

awk -F\" '$4 !~ /eprints.ulster.ac.uk\/view/' file > newfile
  • This worked first time too thanks Jason. For future needs, it would also be helpful to run this as a regex which says ignore letters and only accept figures, i.e. something similar to: awk -F\" '$4 !~ /eprints.ulster.ac.uk\/[a-z]*/' file > newfile – KoreMike Oct 3 '15 at 19:20
  • This would be because I want to ignore urls such as /eprints.ulster.ac.uk/view or /eprints.ulster.ac.uk/author and only accept urls such as /eprints.ulster.ac.uk/16635 – KoreMike Oct 3 '15 at 19:22
  • I achieved this with awk -F\" '$4 !~ /eprints.ulster.ac.uk\/[[:alpha:]]/' uir-index.json > awk-cleaned-index.json – KoreMike Oct 3 '15 at 19:47
  • @KoreMike you could also simplify if you only want digits with: $4 ~ /eprints.ulster.ac.uk\/[[:digit:]]/... – jasonwryan Oct 3 '15 at 21:27
  • Note, though, that json is structured text and, like HTML or XML, can't be reliably parsed with just regular expressions (see stackoverflow.com/questions/1732348/…). The answer here works in this particular simple case but you really should get into the habit of using a specialised json parsing tool - one day you'll run into a json parsing job which you can't do with regexps and awk or sed. jq as mentioned by others here is perfect for that job, it has been described as sed for json. – cas Oct 3 '15 at 23:37
0

A solution with jq.

cat test.json| jq 'select(.id|startswith("eprints.ulster.ac.uk/view/")|not )'

The syntax is quite simple if you are familiar with pipes.

For example

.id|startswith("eprints.ulster.ac.uk/view/")|not

means that is takes the .id field of every object and pipe it through startswith,which returns a boolean and the boolean is negated.

Take a look at the manual from jq for more operators and selectors.

  • Worked first time! jq looks very useful. – KoreMike Oct 3 '15 at 16:48
0

For anybody who is interested I output Raphael's solution to a new JSON file with this command:

cat uir-index.json| jq 'select(.id|startswith("eprints.ulster.ac.uk/view/")|not )' > cleaned-uir-index.json

The output format was back to multiline blocks of code. I ran the same jq command with the "--compact-output / -c" option like this:

cat uir-index.json| jq -c 'select(.id|startswith("eprints.ulster.ac.uk/view/")|not )' > cleaned-uir-index.json

This output the cleaned file in newline format.

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.