How to query Date/Time values stored as string?

[Robert-M-Muench]

I have a JSON with dates fields like:

• "end": "2017-01-01"
• "built-date": "2016-06-30T15:53:44.461+02:00"

and I want to query the JSON using <, >, or == on dates. I think this won't work on plain strings. Hence, can I use a custom modifier to convert the strings into something like a Unix timestamp and compare this?

Or is there a way to provide a custom compare function, which returns true/false?

I first thought to use Result.ForEach to filter my query result. But it stops on the first false.

What is the best approach in such a situation?

[tidwall]

Dates are tough. There isn't a way to do a straight up GJSON query on dates unless they are in lexicographical order or the represented as a numerical timestamp. I think ISO_8601 might work.

For Result.ForEach you'll need to parse each date string

result.ForEach(func(_, value gjson.Result) bool {
    dateValue, _ := time.ParseFormat(value.String(), myDateFormat)
    if dateValue.After(maxDate) && dateValue.Before(maxDate) {
       ...
   }
    return true
})

[Robert-M-Muench]

Some of our dates are in ISO_8601, and we can get most of them into date-only format (throwing away the time as this is not critical in our case). I'm experimenting with this to see if it will work.

To be sure, I understand your Result.ForEach correct:

• You iterate through the complete JSON data-set
• If I want to filter/collect the records that match, I have to track this myself like an index array in gjson.Result?
  • Is there an official way how to do that?

I think it would be great to have a way that I can provide a function, which gets called from GetXXX calls, where I return true/false if the condition matches, and the records get included in the gjson.Result or not.

With this, I could use all the functionality of gjson while being able to hook in my own query operators.

[tidwall]

Is there an official way how to do that?

No official way, but what I usually do is:

var sb strings.Builder
sb.WriteByte('[')
var i int
res.ForEach(_, value gjson.Result) bool {
    if ( /* some condition */ ) {
        // include this value in the result
        if i > 0 {
            sb.WriteByte(',')
        }
        sb.WriteString(value.Raw)
        i++
    }
    return true
})
sb.WriteByte(']')
// Final JSON Array.
result := sb.String() 

I use this pattern for some of the modifiers like @keys and @values.

https://github.com/tidwall/gjson/blob/v1.14.0/gjson.go#L2863-L2886

Another way might be to use sjson.

result := "[]"
res.ForEach(_, value gjson.Result) bool {
    if ( /* some condition */ ) {
        result, _ = sjson.SetRaw(result, "-1", gjson.Raw)
    }
    return true
})

I agree it would be handy to have a dedicated function to filter lists.

Perhaps something like:

lastHour := time.Now().Sub(time.Hour)
var myArray = gjson.Get(json, "messages").Filter(func(value gjson.Result) bool { 
    return value.Get("timestamp").Time().After(lastHour)
})

Which would generate an array of values that match the filter.

BTW, there is a Time() helper method on gjson.Result that auto converts RFC_3339 dates.

https://github.com/tidwall/gjson/blob/v1.14.0/gjson.go#L185-L189