support for restrictions on maps or structs based on AIP-160 syntax

[funcpointer]

Hey folks.. this package is quite neat! Thanks for sharing it.

quick question: The code does not seem like it is able to support maps (like JSONBs) or structs?

type User struct {
	PikaTableName string         `pika:"users"`
	ID            string         `db:"id"`
	Name          string         `db:"name"`
	Age           int32          `db:"age"`
	Active        bool           `db:"active"`
	Score         float64        `db:"score"`
	CreatedAt     time.Time      `db:"created_at"`
	Tags          pq.StringArray `db:"tags"`
	Roles         pq.StringArray `db:"roles"`
	Preferences   []byte         `db:"preferences"`
	Metadata      []byte         `db:"metadata"`
}

the : operator on repeating cases works well. e.g., name = "Bob Smith" AND roles:"admin" works where roles is an array.

For the JSONB case:

e.g.,

		// JSONB filters
		// Check if key exists using JSONB operators
		`metadata:department`, // Check if department key exists

		// Check for specific values using JSONB operators
		`metadata.department:"engineering"`, // Check if department is "engineering"

These filters fail with Error applying filter: unexpected identifier department.

Before I try to dig deeper, is this a known issue? Are there any workarounds?

[funcpointer]

-- Drop tables if they exist
DROP TABLE IF EXISTS user_addresses;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS addresses;

-- Create users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    age INTEGER,
    active BOOLEAN NOT NULL DEFAULT true,
    score DOUBLE PRECISION,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    tags TEXT[] DEFAULT '{}'::text[],
    roles TEXT[] DEFAULT '{}'::text[],
    preferences JSONB DEFAULT '{}'::jsonb,
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Create addresses table
CREATE TABLE addresses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    street VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    country VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20),
    type VARCHAR(50) NOT NULL, -- home, work, etc.
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create junction table for users and addresses
CREATE TABLE user_addresses (
    user_id UUID NOT NULL REFERENCES users(id),
    address_id UUID NOT NULL REFERENCES addresses(id),
    is_primary BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, address_id)
);

-- Seed addresses first
INSERT INTO addresses (id, street, city, state, country, postal_code, type, created_at) VALUES
    ('11111111-1111-1111-1111-111111111111', '123 Main St', 'New York', 'NY', 'USA', '10001', 'home', '2024-01-15T10:00:00Z'),
    ('22222222-2222-2222-2222-222222222222', '456 Market St', 'San Francisco', 'CA', 'USA', '94103', 'work', '2024-02-01T10:00:00Z'),
    ('33333333-3333-3333-3333-333333333333', '789 Pine St', 'Seattle', 'WA', 'USA', '98101', 'home', '2024-02-10T10:00:00Z'),
    ('44444444-4444-4444-4444-444444444444', '321 Oak St', 'Boston', 'MA', 'USA', '02108', 'work', '2024-03-01T10:00:00Z'),
    ('55555555-5555-5555-5555-555555555555', '555 Park Ave', 'Austin', 'TX', 'USA', '78701', 'home', '2024-03-15T10:00:00Z');

-- Seed users
INSERT INTO users (
    id, name, age, active, score, created_at, tags, roles, preferences, metadata
) VALUES
    -- Alice Johnson variations (for testing different conditions)
    ('aaaaa111-1111-1111-1111-111111111111', 'Alice Johnson', 33, true, 1.4, '2024-02-06T00:09:36Z', 
     ARRAY['developer', 'golang'], ARRAY['user', 'manager'], 
     '{"theme": "dark"}', '{"lastLogin": "2024-01-01"}'),
    
    ('aaaaa222-2222-2222-2222-222222222222', 'Alice Johnson', 32, false, 1.0, '2024-03-06T07:48:17Z',
     ARRAY['developer', 'golang'], ARRAY['user'],
     '{"theme": "light"}', NULL),

    -- Bob Smith variations (for testing array and boolean conditions)
    ('bbbbb111-1111-1111-1111-111111111111', 'Bob Smith', 56, true, 0.6, '2024-02-14T09:47:34Z',
     ARRAY['developer', 'python'], ARRAY['user', 'manager'],
     '{}', '{"department": "engineering"}'),
    
    ('bbbbb222-2222-2222-2222-222222222222', 'Bob Smith', 35, true, 4.2, '2024-02-29T15:53:43Z',
     ARRAY['developer', 'python'], ARRAY['user', 'manager'],
     NULL, NULL),

    -- Jane Doe variations (for testing date and score ranges)
    ('ccccc111-1111-1111-1111-111111111111', 'Jane Doe', 29, true, 2.4, '2024-02-18T04:01:01Z',
     ARRAY['manager', 'team-lead'], ARRAY['user', 'manager'],
     '{"notifications": true}', '{}'),
    
    ('ccccc222-2222-2222-2222-222222222222', 'Jane Doe', 35, true, 1.1, '2024-01-01T16:09:48Z',
     ARRAY['developer', 'python'], ARRAY['admin', 'user'],
     NULL, NULL),

    -- John Doe variations (for testing complex conditions)
    ('ddddd111-1111-1111-1111-111111111111', 'John Doe', 34, true, 2.2, '2024-02-10T09:57:24Z',
     ARRAY['developer', 'golang'], ARRAY['user', 'manager'],
     '{}', '{}'),
    
    ('ddddd222-2222-2222-2222-222222222222', 'John Doe', 25, false, 1.0, '2024-03-06T00:13:39Z',
     ARRAY['developer', 'python'], ARRAY['admin', 'user'],
     NULL, NULL);

-- Create user-address relationships
INSERT INTO user_addresses (user_id, address_id, is_primary, created_at) VALUES
    -- Alice has home and work addresses
    ('aaaaa111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', true, '2024-01-15T10:00:00Z'),
    ('aaaaa111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222', false, '2024-02-01T10:00:00Z'),
    
    -- Bob has one home address
    ('bbbbb111-1111-1111-1111-111111111111', '33333333-3333-3333-3333-333333333333', true, '2024-02-10T10:00:00Z'),
    
    -- Jane has work address
    ('ccccc111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444', true, '2024-03-01T10:00:00Z'),
    
    -- John has home address
    ('ddddd111-1111-1111-1111-111111111111', '55555555-5555-5555-5555-555555555555', true, '2024-03-15T10:00:00Z');

-- Create indexes for better query performance
CREATE INDEX idx_user_addresses_user_id ON user_addresses(user_id);
CREATE INDEX idx_user_addresses_address_id ON user_addresses(address_id);
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_addresses_city ON addresses(city);
CREATE INDEX idx_addresses_type ON addresses(type); 

and

package main

import (
	"context"
	"fmt"
	"os"
	"time"

	"github.com/lib/pq"
	"go.ciq.dev/pika"
)

type User struct {
	PikaTableName string         `pika:"users"`
	ID            string         `db:"id"`
	Name          string         `db:"name"`
	Age           int32          `db:"age"`
	Active        bool           `db:"active"`
	Score         float64        `db:"score"`
	CreatedAt     time.Time      `db:"created_at"`
	Tags          pq.StringArray `db:"tags"`
	Roles         pq.StringArray `db:"roles"`
	Preferences   []byte         `db:"preferences"`
	Metadata      []byte         `db:"metadata"`
}

type Address struct {
	PikaTableName string    `pika:"addresses"`
	ID            string    `db:"id"`
	Street        string    `db:"street"`
	City          string    `db:"city"`
	State         string    `db:"state"`
	Country       string    `db:"country"`
	PostalCode    string    `db:"postal_code"`
	Type          string    `db:"type"`
	CreatedAt     time.Time `db:"created_at"`
}

type UserAddress struct {
	PikaTableName string    `pika:"user_addresses"`
	UserID        string    `db:"user_id"`
	AddressID     string    `db:"address_id"`
	IsPrimary     bool      `db:"is_primary"`
	CreatedAt     time.Time `db:"created_at"`
}

type UserWithAddresses struct {
	User
	Addresses []Address `db:"addresses"`
}

func main() {
	ctx := context.Background()

	// Connect to PostgreSQL using pgx
	connString := "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
	db, err := pika.NewPostgreSQL(connString)
	if err != nil {
		fmt.Printf("Failed to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer db.Close()

	// Test join functionality
	fmt.Println("\n=== Testing Join Functionality ===")

	// Basic join through the junction table
	qs := pika.Q[User](db)
	qs = qs.InnerJoin(User{}, UserAddress{}, "id", "user_id").
		InnerJoin(UserAddress{}, Address{}, "address_id", "id")

	query, args := qs.AllQuery()
	fmt.Printf("\nBasic Join Query:\n%s\nArgs: %v\n", query, args)

	// Join with conditions
	qs = pika.Q[User](db)
	qs = qs.InnerJoin(User{}, UserAddress{}, "id", "user_id").
		InnerJoin(UserAddress{}, Address{}, "address_id", "id")
	qs, err = qs.AIP160(`name = "Bob Smith" AND city = "New York"`, pika.AIPFilterOptions{})

	query, args = qs.AllQuery()
	fmt.Printf("\nJoin with Conditions Query:\n%s\nArgs: %v\n", query, args)

	// Left join
	qs = pika.Q[User](db)
	qs = qs.LeftJoin(User{}, UserAddress{}, "id", "user_id").
		LeftJoin(UserAddress{}, Address{}, "address_id", "id")

	query, args = qs.AllQuery()
	fmt.Printf("\nLeft Join Query:\n%s\nArgs: %v\n", query, args)

	// Multiple joins with conditions
	qs = pika.Q[User](db)
	qs = qs.InnerJoin(User{}, UserAddress{}, "id", "user_id").
		InnerJoin(UserAddress{}, Address{}, "address_id", "id")
	qs, err = qs.AIP160(`active = true AND type = "home" AND created_at > "2024-01-01T00:00:00Z"`, pika.AIPFilterOptions{})

	query, args = qs.AllQuery()
	fmt.Printf("\nMultiple Joins with Conditions Query:\n%s\nArgs: %v\n", query, args)

	// Try to execute the queries
	users, err := qs.All(ctx)
	if err != nil {
		fmt.Printf("Error executing query: %v\n", err)
	} else {
		fmt.Printf("\nSuccess! Found %d users with addresses\n", len(users))
		for _, user := range users {
			fmt.Printf("User: %v, Age: %d, Active: %v, Score: %.1f\n",
				user.Name, user.Age, user.Active, user.Score)
		}
	}

	// Original filter tests...
	filters := []string{
		// Basic filters
		`name = "Alice Johnson"`,
		`age >= 30`,
		`active = true`,
		`score < 2.0`,

		// Array filters - single value membership
		`tags:"developer"`,
		`roles:"admin"`,
		`tags:"python"`,
		`roles:"user"`,

		// Date filters
		`created_at > "2024-02-01T00:00:00Z"`,

		// AND combinations with array filters
		`name = "Bob Smith" AND active = true`,
		`name = "Alice Johnson" AND tags:"developer"`,
		`name = "Bob Smith" AND roles:"admin"`,
		`name = "Alice Johnson" AND age >= 30 AND score < 2.0`,

		// Simple OR combinations (no array ORs)
		`name = "Bob Smith" OR age > 30`,

		// Complex combinations
		`name = "Bob Smith" AND (tags:"developer")`,
		`(name = "Bob Smith" OR age > 30) AND active = true`,

		// Null checks
		`metadata = null`,
		`preferences = null AND active = true`,

		// Simple negations (no array negations)
		`-(name = "Bob Smith")`,
		`-(age >= 30 AND active = true)`,
		`-(tags:"python" OR roles:"admin")`,

		// JSONB filters
		// Check if key exists (both forms are equivalent)
		`metadata:department`,   // Check if department key exists
		`metadata.department:*`, // Check if department key exists (alternative form)
		`preferences:theme`,     // Check if theme key exists
		`preferences.theme:*`,   // Check if theme key exists (alternative form)
		// Check for specific values
		`metadata.department:"engineering"`, // Check if department is "engineering"
		`preferences.theme:"dark"`,          // Check if theme is "dark"
	}

	// Define filter options with array fields marked as repeated
	options := pika.AIPFilterOptions{
		Identifiers: map[string]pika.AIPFilterIdentifier{
			"tags": {
				IsRepeated: true,
				ColumnName: "tags",
			},
			"roles": {
				IsRepeated: true,
				ColumnName: "roles",
			},
			// Add JSONB field configurations
			"metadata": {
				ColumnName: "department",
			},
			"preferences": {
				ColumnName: "theme",
			},
		},
		// List all acceptable identifiers
		AcceptableIdentifiers: []string{
			"name", "age", "active", "score", "created_at",
			"tags", "roles", "metadata", "preferences",
		},
	}

	// Apply and test each filter separately
	for _, filter := range filters {
		// Create a fresh query set for each test
		qs := pika.Q[User](db)

		fmt.Printf("\n=== Testing filter: %s ===\n", filter)

		qs, err = qs.AIP160(filter, options)
		if err != nil {
			fmt.Printf("Error applying filter: %v\n", err)
			continue
		}

		// Get and print the SQL query
		query, args := qs.AllQuery()
		fmt.Printf("SQL Query: %s\n", query)
		fmt.Printf("Arguments: %v\n", args)

		// Try to execute the query
		users, err := qs.All(ctx)
		if err != nil {
			fmt.Printf("Error executing query: %v\n", err)
			continue
		}

		fmt.Printf("Success! Found %d users\n", len(users))
		for _, user := range users {
			fmt.Printf("User: %v, Age: %d, Active: %v, Score: %.1f, Tags: %v, Roles: %v\n",
				user.Name, user.Age, user.Active, user.Score, user.Tags, user.Roles)
		}
	}
}

can be used for reproducing the issue.

[WestleyK]

Hello @funcpointer, it looks like JSONB/[]byte is not a supported type (yet) https://github.com/ctrliq/pika/blob/main/pika_aip_filter_proto.go

CC @randyhongo @clowenhg if you can provide more details.