Find where subquery

[chandraanwar91]

I h've a problem when try so search condition with subquery.

SELECT * FROM coupons WHERE ((start_date >= '2018-12-01 00:00:00' AND end_date <= '2018-12-01 23:59:59') OR(start_date >= '2018-12-10 00:00:00' AND end_date <= '2018-12-10 23:59:59'))

If i want to search with example query like this . Can you please help how to filter condition like this?

[thedevsaddam]

Can you verify the sample I/O. It seems okay, please check and let me know:

Code:

package main

import (
    "fmt"
    "time"

    "github.com/davecgh/go-spew/spew"
    "github.com/thedevsaddam/gojsonq"
)

const (
    layout = "2006-01-02"
    data   = `{
  "cupons":[
    {
      "name":"50% off",
      "start_date":"2018-10-10",
      "end_date":"2018-10-12"
    },
    {
      "name":"free T-Shirt",
      "start_date":"2018-12-10",
      "end_date":"2018-12-20"
    },
    {
      "name":"20% off",
      "start_date":"2018-08-10",
      "end_date":"2018-09-20"
    },
    {
      "name":"free smart watch",
      "start_date":"2018-10-14",
      "end_date":"2018-10-18"
    }
  ]
}`
)

func dateLessOrEqualTo(x, y interface{}) (bool, error) { // date less than or equal query function
    xs, okx := x.(string)
    ys, oky := y.(string)
    if !okx || !oky {
        return false, fmt.Errorf("date support for string only")
    }

    t1, _ := time.Parse(layout, xs)
    t2, _ := time.Parse(layout, ys)

    return t1.Unix() <= t2.Unix(), nil
}

func dateGreaterOrEqualTo(x, y interface{}) (bool, error) { // date greater than or equal query function
    xs, okx := x.(string)
    ys, oky := y.(string)
    if !okx || !oky {
        return false, fmt.Errorf("date support for string only")
    }

    t1, _ := time.Parse(layout, xs) // TODO: check these error too
    t2, _ := time.Parse(layout, ys)

    return t1.Unix() >= t2.Unix(), nil
}

func main() {
    jq := gojsonq.New().JSONString(data).From("cupons")
    jq.Macro("date<=", dateLessOrEqualTo)
    jq.Macro("date>=", dateGreaterOrEqualTo)

    jq.Where("start_date", "date>=", "2018-10-10")
    jq.Where("end_date", "date<=", "2018-10-13")

    jq.OrWhere("start_date", "date>=", "2018-08-10")
    jq.Where("end_date", "date<=", "2018-09-21")

    spew.Dump(jq.Get())
}

Output:

([]interface {}) (len=2 cap=2) {
 (map[string]interface {}) (len=3) {
  (string) (len=4) "name": (string) (len=7) "50% off",
  (string) (len=10) "start_date": (string) (len=10) "2018-10-10",
  (string) (len=8) "end_date": (string) (len=10) "2018-10-12"
 },
 (map[string]interface {}) (len=3) {
  (string) (len=4) "name": (string) (len=7) "20% off",
  (string) (len=10) "start_date": (string) (len=10) "2018-08-10",
  (string) (len=8) "end_date": (string) (len=10) "2018-09-20"
 }
}

[thedevsaddam]

@chandraanwar91 As there is no activity on the issue, closing it.