How to select a date range from a JSON string by using jq?

Question

I have a JSON string like this (MacOS):

[{
    "id": 3624,
    "created_at": "2016-10-21T20:51:16.000+08:00",
  },
  {
     "id": 3625,
    "created_at": "2016-10-22T08:09:16.000+08:00",
  },
 {
     "id": 3626,
    "created_at": "2016-10-23T09:19:55.000+08:00",
  }]

I wanna select "created_at" from "2016-10-21" to "2016-10-22"; I wanna get result like this:

[{
    "id": 3624,
    "created_at": "2016-10-21T20:51:16.000+08:00",
  },
  {
     "id": 3625,
    "created_at": "2016-10-22T08:09:16.000+08:00",
  }]

Can someone point me in the right direction?

The problem is solved. Now,i use this code to select date right to the minute,i hope it's useful for others:

jq --arg s '2016-10-26T18:16' --arg e '2016-10-27T20:24' '[($s, $e) | strptime("%Y-%m-%dT%H:%M") | mktime] as $r
  | map(select(
        (.updated_at[:19] | strptime("%Y-%m-%dT%H:%M:%S") | mktime) as $d
          | $d >= $r[0] and $d <= $r[1]))' <<< "$requestJson"

Answer 1

Using command-line JSON parser jq, as requested:

^{Note: Jeff Mercado's helpful answer demonstrates a lot of great advanced jq techniques, but for the specific problem at hand I believe that the text-based approach in this answer is much simpler while still being flexible enough.}

#!/usr/bin/env bash

# Create variable with sample input.
IFS= read -r -d '' json <<'EOF'
[
  {
    "id": 3624,
    "created_at": "2016-10-21T20:51:16.000+08:00"
  },
  {
     "id": 3625,
    "created_at": "2016-10-22T08:09:16.000+08:00"
  },
  {
     "id": 3626,
    "created_at": "2016-10-23T09:19:55.000+08:00"
  }
]
EOF

# Use `jq` to select the objects in the array whose .created_at
# property value falls between "2016-10-21:T20:51" and "2016-10-22T08:09"
# and return them as an array (effectively a sub-array of the input).
# (To solve the problem as originally stated, simply pass "2016-10-21" 
#  and "2016-10-22" instead.)
jq --arg s '2016-10-21T20:51' --arg e '2016-10-22T08:09' '
  map(select(.created_at | . >= $s and . <= $e + "z"))
' <<<"$json"

• Arguments --arg s '2016-10-21T20:51' and --arg e '2016-10-22T08:09' define variables $s (start of date+time range) and $e (end of date+time range) respectively, for use inside the jq script.

• Function map() applies the enclosed expression to all the elements of the input array and outputs the results as an array, too.

• Function select() accepts a filtering expression: every input object is evaluated against the enclosed expression, and the input object is only passed out if the expression evaluates to a "truthy" value.

• Expression .created_at | . >= $s and . <= $e + "z" accesses each input object's created_at property and sends its value to the comparison expression, which performs lexical comparison, which - due to the formatting of the date+time strings - amounts to chronological comparison.

  • Note the trailing "z" appended to the range endpoint, to ensure that it matches all date+time strings in the JSON string that prefix-match the endpoint; e.g., endpoint 2016-10-22T08:09 should match 2016-10-22T08:09:01 as well as 2016-10-22T08:59.

  • This lexical approach allows you to specify as many components from the beginning as desired in order to narrow or widen the date range; e.g. --arg s '2016-10-01' --arg e '2016-10-31' would match all entries for the entire month of October 2016.

Answer 2

For a more robust solution, it would be better to parse the dates to get its components and compare those components. The closest you can get is to use strptime/1 to parse the date which returns an array of its components. Then compare the components to check if it's in range.

The array that strptime returns are the components:

year (%Y)
month (%m)
date (%d)
hours (%H)
minutes (%M)
seconds (%S)
day of week (%w)
day of year (%j)

Since you're only comparing the dates, the comparisons should only look at the first 3 components.

$ jq --arg s '2016-10-21' --arg e '2016-10-22' '
[($s, $e) | strptime("%Y-%m-%d")[0:3]] as $r
  | map(select(
        (.created_at[:19] | strptime("%Y-%m-%dT%H:%M:%S")[0:3]) as $d
          | $d >= $r[0] and $d <= $r[1]
    ))
' input.json

Since you're running on a Mac, I'd expect these methods would be available to you in your build. You may have to make adjustments to the format of the dates for it to work as expected. As you can see in the comments, we had to massage it a bit to make it work.