search in array column #861

moinologics · 2023-12-31T10:18:15Z

moinologics
Dec 31, 2023

add support to search in an array column

example -

table users

id	name	blocked_user_ids
1	john	[3]
2	jane	[1,3]
3	bob	[]

resultant query

SELECT name FROM users WHERE {{id}} = ANY(blocked_user_ids)

when id = 1, return jane
when id = 3, return john & jane

avelino · 2023-12-31T13:58:38Z

avelino
Dec 31, 2023
Maintainer

Apparently the ANY() function has not been implemented, see here in the documentation

https://docs.prestd.com/api-reference/parameters#functions-support

@moinologics can you implement it? Would be here:

prest/adapters/postgres/postgres.go

Lines 1531 to 1550 in 9f4f283

    
           func NormalizeGroupFunction(paramValue string) (groupFuncSQL string, err error) { 
        
           	values := strings.Split(paramValue, ":") 
        
           	groupFunc := strings.ToUpper(values[0]) 
        
           	switch groupFunc { 
        
           	case "SUM", "AVG", "MAX", "MIN", "STDDEV", "VARIANCE": 
        
           		// values[1] it's a field in table 
        
           		v := values[1] 
        
           		if v != "*" { 
        
           			values[1] = fmt.Sprintf(`"%s"`, v) 
        
           		} 
        
           		groupFuncSQL = fmt.Sprintf(`%s(%s)`, groupFunc, values[1]) 
        
           		if len(values) == 3 { 
        
           			groupFuncSQL = fmt.Sprintf(`%s AS "%s"`, groupFuncSQL, values[2]) 
        
           		} 
        
           		return 
        
           	default: 
        
           		err = errors.Wrapf(ErrInvalidGroupFn, "%s", groupFunc) 
        
           		return 
        
           	} 
        
           }

0 replies

avelino · 2023-12-31T14:04:18Z

avelino
Dec 31, 2023
Maintainer

SELECT name FROM users WHERE {{id}} = ANY(blocked_user_ids)

@moinologics to solve your problem without implementing the feature in the prestd core you can make custom queries, see the documentation here

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

search in array column #861

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Replies: 2 comments

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Uh oh!

search in array column #861

Uh oh!

Uh oh!

moinologics Dec 31, 2023

Replies: 2 comments

Uh oh!

avelino Dec 31, 2023 Maintainer

Uh oh!

avelino Dec 31, 2023 Maintainer

moinologics
Dec 31, 2023

avelino
Dec 31, 2023
Maintainer

avelino
Dec 31, 2023
Maintainer