how to check if a JSONB column contains a value -- CONTAINS?

[tacman]

I'm sure this is simple, I'll update the docs once I know how to do it. A simple query:

            $qb = $this->articleRepository->createQueryBuilder('article');
            $qb
                ->andWhere('CONTAINS(article.tagValues, :tagValue) = TRUE')
                ->setParameter('tagValue', $tagValue)
                ->setMaxResults($limit);
            ;

tagValues is a JSON column

    #[ORM\Column(type: Types::JSON,  nullable: true, options: ['jsonb' => true])]
    private ?array $tagValues = null;

/c db:run "select tag_values from article limit 1"
 ----------------------------- 
  tag_values                   
 ----------------------------- 
  ["California", "Rob Bonta"]  
 ----------------------------- 

The SQL generated:

SELECT a0_.headline AS headline_0, a0_.summary AS summary_1, a0_.thumbnail_url AS thumbnail_url_2, a0_.url AS url_3, a0_.code AS code_4, a0_.bing AS bing_5, a0_.date_published AS date_published_6, a0_.bing_about AS bing_about_7, a0_.countries AS countries_8, a0_.tags AS tags_9, a0_.tag_count AS tag_count_10, a0_.tag_values AS tag_values_11, a0_.media_id AS media_id_12, a0_.category_id AS category_id_13 FROM article a0_ WHERE (a0_.tag_values @> ?) = true ORDER BY a0_.date_published DESC LIMIT 50

The error:

An exception occurred while executing a query: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type json

DETAIL:  Token "Bogotá" is invalid.

CONTEXT:  JSON data, line 1: Bogotá

unnamed portal parameter $1 = '...

Do I need to cast the string I'm searching for? Or maybe CONTAINS is the wrong word, but should be a JSONB_ call?

Thanks.

[martin-georgiev]

@coderabbitai - can you help here?

[coderabbitai[bot]]

[!TIP] For best results, initiate chat on the files or code changes.

@martin-georgiev I'd be happy to help with this JSONB query issue in PostgreSQL with Doctrine!

The problem is in how you're passing the parameter to the JSONB contains operator. When using the @> operator (which is what CONTAINS translates to), PostgreSQL expects the right-hand value to be formatted as valid JSON.

Here are a few solutions:

1. Use the ? operator (Recommended)

The ? operator checks if a string exists as an array element, which is exactly what you want:

$qb = $this->articleRepository->createQueryBuilder('article');
$qb
    ->andWhere('article.tagValues ? :tagValue')
    ->setParameter('tagValue', $tagValue)
    ->setMaxResults($limit);

2. Format the parameter as JSON array

If you want to use the @> operator, you need to format your parameter as a JSON array:

$qb = $this->articleRepository->createQueryBuilder('article');
$qb
    ->andWhere('article.tagValues @> :tagValue')
    ->setParameter('tagValue', json_encode([$tagValue]))
    ->setMaxResults($limit);

3. Using Doctrine PostgreSQL functions

If you're using your own PostgreSQL extension for Doctrine (which your GitHub repo suggests), you could register a custom DQL function for JSONB operations:

$qb = $this->articleRepository->createQueryBuilder('article');
$qb
    ->andWhere('JSONB_CONTAINS_ELEMENT(article.tagValues, :tagValue) = true')
    ->setParameter('tagValue', $tagValue)
    ->setMaxResults($limit);

The second option specifically solves your issue with "Bogotá" as it properly encodes the UTF-8 characters into valid JSON format.

[martin-georgiev]

@coderabbitai where did this JSONB_CONTAINS_ELEMENT come from? Are you sure you understand Doctrine and how this repository's custom operators are registered and meant to be used? Inspect the doc/ folder for some more insights.

[coderabbitai[bot]]

[!TIP] For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
# First, let's check if there's a doc/ folder and what files it contains
echo "Checking for documentation files:"
find doc -type f | sort

# Let's examine the content of any relevant documentation files
echo -e "\nExamining documentation content:"
find doc -name "*.md" -o -name "*.rst" -o -name "*.txt" | xargs cat

# Look for any JSONB-related functions or operators in the codebase
echo -e "\nSearching for JSONB-related code:"
rg -i "jsonb|json.*operator|json.*function" --type php

# Check for DQL functions registration
echo -e "\nLooking for DQL function registration:"
rg -i "registerfunction|add.*function" --type php

# Look specifically for any operator related to JSON contains
echo -e "\nLooking for JSON contains operators:"
rg -i "contain|@>" --type php

Length of output: 70944

---

🏁 Script executed:

#!/bin/bash
# Let's check for README or other documentation files
echo "Checking for README and other documentation files:"
find . -name "*.md" | sort

# Now let's examine the README content
echo -e "\nExamining README content:"
cat README.md 2>/dev/null

# Let's look for the implementation of the Contains function since we found it in the tests
echo -e "\nExamining the Contains function implementation:"
rg -A 10 "class Contains" --type php

# Let's check for implementations related to JSON array operators ? and ?| (contains element)
echo -e "\nLooking for JSON array element operators implementation:"
rg -i "(\?\||@>|\?)" --type php

Length of output: 37791

---

🏁 Script executed:

#!/bin/bash
# Let's look at the AVAILABLE-FUNCTIONS-AND-OPERATORS.md file to understand the correct JSONB operators
echo "Examining AVAILABLE-FUNCTIONS-AND-OPERATORS.md:"
cat docs/AVAILABLE-FUNCTIONS-AND-OPERATORS.md

# Let's also look for the TheRightExistsOnTheLeft function implementation since it uses the ?? operator
echo -e "\nExamining TheRightExistsOnTheLeft implementation:"
cat src/MartinGeorgiev/Doctrine/ORM/Query/AST/Functions/TheRightExistsOnTheLeft.php

# Let's check the doc examples directory if it exists
echo -e "\nChecking for examples in documentation:"
cat docs/USE-CASES-AND-EXAMPLES.md 2>/dev/null

Length of output: 10814

---

@martin-georgiev I've examined your PostgreSQL-for-doctrine library to understand how to properly query JSONB arrays. According to your repository's documentation, there are specific DQL functions for working with JSONB data.

For your case with the JSONB array column containing strings like ["California", "Rob Bonta"], you want to check if a specific value exists in the array. The error you're getting with "Bogotá" is because the CONTAINS function (which maps to PostgreSQL's @> operator) requires the parameter to be formatted as a valid JSON array.

Here are your options:

1. Use RIGHT_EXISTS_ON_LEFT (PostgreSQL's ? operator)

This is the ideal solution for checking if a string exists as an array element:

$qb = $this->articleRepository->createQueryBuilder('article');
$qb
    ->andWhere('RIGHT_EXISTS_ON_LEFT(article.tagValues, :tagValue) = TRUE')
    ->setParameter('tagValue', $tagValue)
    ->setMaxResults($limit);

2. If you want to stick with CONTAINS, format parameter as JSON array

$qb = $this->articleRepository->createQueryBuilder('article');
$qb
    ->andWhere('CONTAINS(article.tagValues, :tagValue) = TRUE')
    ->setParameter('tagValue', json_encode([$tagValue]))
    ->setMaxResults($limit);

The key insight from your library's documentation is that these operator functions need to be used with = TRUE since Doctrine doesn't directly support custom operators.

---

✏️ Learnings added

Learnt from: martin-georgiev
URL: https://github.com/martin-georgiev/postgresql-for-doctrine/issues/253

Timestamp: 2025-03-11T17:02:50.257Z
Learning: The PostgreSQL-for-doctrine library implements the JSON array element existence check using the `RIGHT_EXISTS_ON_LEFT` DQL function, which maps to PostgreSQL's `?` operator. This is used for checking if a specific value exists in a JSONB array column.

---

Learnt from: martin-georgiev
URL: https://github.com/martin-georgiev/postgresql-for-doctrine/issues/253

Timestamp: 2025-03-11T17:02:50.257Z
Learning: When using operator-like functions in PostgreSQL-for-doctrine, they must be used as boolean functions with `= TRUE` rather than as direct operators (e.g., `RIGHT_EXISTS_ON_LEFT(column, value) = TRUE` instead of `column RIGHT_EXISTS_ON_LEFT value`).

Note: Learnings are effective only in the context of similar code segments. To apply general coding guidelines, please configure review instructions. You can manage existing learnings in the app.

[martin-georgiev]

@tacman check the above message and close if the issue is resolved.