Get JSON files from particular interval based on date field

Question

I've a lot json file the structure of which looks like below:

{
  key1: 'val1'
  key2: {
          'key21': 'someval1',
          'key22': 'someval2',
          'key23': 'someval3',
          'date': '2018-07-31T01:30:30Z',
          'key25': 'someval4'
  }
  key3: []
  ... some other objects
 }          

My goal is to get only these files where date field is from some period. For example from 2018-05-20 to 2018-07-20. I can't base on date of creation this files, because all of this was generated in one day. Maybe it is possible using sed or similar program?

Answer 1

Fortunately, the date in this format can be compared as a string. You only need something to parse the JSONs, e.g. Perl:

perl -l -0777 -MJSON::PP -ne '
   $date = decode_json($_)->{key2}{date};
   print $ARGV if $date gt "2018-07-01T00:00:00Z";
' *.json

• -0777 makes perl slurp the whole files instead of reading them line by line
• -l adds a newline to print
• $ARGV contains the name of the currently processed file

See JSON::PP for details. If you have JSON::XS or Cpanel::JSON::XS, you can switch to them for faster processing.

I had to fix the input (replace ' by ", add commas, etc.) in order to make the parser happy.

Answer 2

If your files actually contain valid JSON, the task can be accomplished in a one-liner with jq, e.g.:

jq 'if .key2.date[0:10] | (. >= "2018-05-20" and . <= "2018-07-31") then input_filename else empty end' *.json

This is just an illustration. jq has date-handling functions for dealing with more complex requirements.

Handling quasi-JSON

If your files contain quasi-JSON, then you could use jq in conjunction with a JSON rectifier. If your sample is representative, then hjson could be used, e.g.

for f in *.qjson
do
  hjson -j $f | jq --arg f "$f" '
    if .key2.date[0:7] == "2018-07" then $f else empty end'
done

Answer 3

Try like this:

1. Find a online converter. (for example: https://codebeautify.org/json-to-excel-converter#) and convert Json to CSV

2. Open CSV file with Excel

3. Filter your data