Support JSON pagination

[nikicc]

Fixes #42.

Description

This is a WIP implementation of enabling pagination across data inside JSON columns. @pilagod I wanted to get your thoughts on this to see if this is something you are comfortable proceeding with. Please let me know 🙏

If we are happy to proceed, I would add the tests for the new functionality and document it as well in the readme.

How to test

1. Define the Custom Type

First we need to define our type for JSON which also implements the CustomTypePaginator interface. Something like this should do:

import (
    "gorm.io/datatypes"
)

type MyJSON struct {
    datatypes.JSON
}

func (j MyJSON) GetCustomTypeValue(meta interface{}) interface{} {
    // The decoding & mapping logic should be implemented by custom type provider
    d := map[string]interface{}{}
    err := json.Unmarshal(j.JSON, &d)
    if err != nil {
        panic("TODO")
    }

    item := d[meta.(string)]

         // remove quotes from string for SQL comparisons
    if s, ok := item.(string); ok {
        return strings.ReplaceAll(s, "\"", "")
    }

    return item
}

2. Define GORM model & data structure

Let our struct be like this:

type Item struct {
    ID     string       `gorm:"primaryKey,column:id" json:"id"`
    Value  string       `gorm:"column:value" json:"value"`
    Number float64      `gorm:"column:number" json:"number"`
    Data   MyJSONB      `gorm:"column:data" json:"data"`
}

And let the data field in the DB contains JSONs like this:

{
    "k1": "a",
    "k2": 1.0
}

3. Define pagination rules

To paginate on k1 ascending, we would need a rule like this:

rule = paginator.Rule{
    Key:             "Data",
    Order:           paginator.ASC,
    SQLRepr:         "data #>> '{k1}'",
    NULLReplacement: "",
    CustomType: &paginator.CustomType{
        Meta:    "k1",
        Type:    reflect.PtrTo(reflect.TypeOf("")),
        SQLType: "text",
    },
}

To paginate on k2 descending, we would need a rule like this:

rule = paginator.Rule{
    Key:             "Data",
    Order:           paginator.DESC,
    SQLRepr:         "data #>> '{k2}'",
    NULLReplacement: 0.0,
    CustomType: &paginator.CustomType{
        Meta:    "k2",
        Type:    reflect.PtrTo(reflect.TypeOf(1.0)),
        SQLType: "numeric",
    },
}

Concerns

1. CustomType struct needs a few extra information. In order to be able to type cast in the encoder, decoder and SQL, we need to provide:
   1. meta: the meta attribute path on the custom type objects (in case of JSONs, this would be the key)
   2. type: Golang type for the decoded to be able to cast it to a correct type
   3. SQLType: to make type casting in SQL. By default, the when you use JSON operators like #>> the returned type will be JSON (see the note in the docs), hence in order for comparisons to work correctly we need to type cast it to the correct type.

[coveralls]

Pull Request Test Coverage Report for Build 208

• 53 of 53 (100.0%) changed or added relevant lines in 3 files are covered.
• No unchanged relevant lines lost coverage.
• Overall coverage increased (+0.2%) to 97.312%

---

Totals
Change from base Build 197:	0.2%
Covered Lines:	362
Relevant Lines:	372

---

💛 - Coveralls

[nikicc]

I have no further questions and I think this PR is ready to merge. 💪 You can check again if there is anything missing. If everything is ok, then I will merge this PR. 🎉

I just pushed one more tiny fix in one of the comments. With that, I think this is ready to be merged 🎉

It is fabulous to support new custom type without affecting current users.

I'm excited as well. Will start using this for our JSON pagination as soon as the release is out.

@nikicc Thanks you for this great work 💪

Thank you for all the feedback & reviewing 🙏

[pilagod]

No problem. Let's merge it and do an upgrade 😎