SQL builder fro JSON_CONTAINS to search multiple values in a JSON array

[avishbran]

Your Question

Is there a way to build an SQL query that searches multiple-values (not multiple keys) in a JSON array?

SELECT * FROM table WHERE JSON_CONTAINS(obj->'$.array_of_ints', [1,2,3]);

for: { "obj": {"array_of_ints":[1,2,3,4]} } the result will be TRUE.

Or:

SELECT * FROM table WHERE JSON_CONTAINS(obj->'$.array_of_ints', 2);

for: { "obj":{"array_of_ints":[1,2,3,4]} } the result will be TRUE.

The document you expected this should be explained

https://gorm.io/docs/data_types.html

Expected answer

Would like to know the methods to generate a JSON_CONTAINS SQL query using SQLBuilder

[MuhmdHsn313]

It's very important, and how we can application IN statement that in SQL with JSON query?

[itswuanran]

Your Question

Is there a way to build an SQL query that searches multiple-values (not multiple keys) in a JSON array?

SELECT * FROM table WHERE JSON_CONTAINS(obj->'$.array_of_ints', [1,2,3]);

for: { "obj": {"array_of_ints":[1,2,3,4]} } the result will be TRUE.

Or:

SELECT * FROM table WHERE JSON_CONTAINS(obj->'$.array_of_ints', 2);

for: { "obj":{"array_of_ints":[1,2,3,4]} } the result will be TRUE.

The document you expected this should be explained

https://gorm.io/docs/data_types.html

Expected answer

Would like to know the methods to generate a JSON_CONTAINS SQL query using SQLBuilder

// Build implements clause.Expression
func (json *JSONArrayExpression) Build(builder clause.Builder) {
	if stmt, ok := builder.(*gorm.Statement); ok {
		switch stmt.Dialector.Name() {
		case "mysql":
			builder.WriteString("JSON_CONTAINS (" + stmt.Quote(json.column) + ", JSON_ARRAY(")
			builder.AddVar(stmt, json.equalsValue)
			builder.WriteString("))")
		}
	}
}

datatypes.JSONArrayExpression append a JSON_ARRAY('') string in Build method, and this does not support slice value, I finally found a solution to this problem.

package cdatatypes

import (
	"gorm.io/gen/field"
	"gorm.io/gorm/clause"
)

type ExprCond struct {
	clause.Expr
	field.String
}

func Cond(expr clause.Expr) *ExprCond {
	return &ExprCond{
		Expr:   expr,
		String: field.String{},
	}
}

func (c *ExprCond) BeCond() interface{} { return c.Expr }

func (c *ExprCond) CondError() error { return nil }

Then use the code to support custom conditions. And this cond implements gen.Condition interface

cdatatypes.Cond(gorm.Expr("JSON_CONTAINS (`tag`, ?)", tags))

[abhishek1140yadav]

I created new type that implemented the gorm.Valuer interface

import (
	"code.byted.org/gopkg/lang/slices"
	"context"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"strings"
)

type SliceOfString []string

func (s SliceOfString) GormValue(context.Context, *gorm.DB) clause.Expr {
	return clause.Expr{SQL: strings.Join(slices.MapString(s, func(s string) string { return "'" + s + "'" }), ", ")}
}

and used the following condition code

var list SliceOfString
.
.
.
Where(gen.Cond(datatypes.JSONArrayQuery("column-name").Contains(list))...)