[Bug]: Retrieving PostgreSQL Data with JSONB Column Query Fails

[chalindukodikara]

Description

public isolated function searchKind() returns error? {

    int port = 8080;

    sql:ParameterizedQuery kindSearchQuery =
            `SELECT * FROM component WHERE (kind #> Array['spec', 'build', 'buildpack', 'port']) @> ${port}`;
    stream<ComponentKindResultStream, sql:Error?> componentKindResultStream = dbClient->query(kindSearchQuery);
    ComponentKindResultStream[]|error componentKinds = from var x in componentKindResultStream
        let var idValue = x.id, var kindValue = x.kind
        group by idValue, kindValue
        select {
            id: idValue,
            kind: kindValue
        };
    io:println(componentKinds);

}

type ComponentKindResultStream record {
    string id;
    string kind;
};

Following query fails with the below error. error DatabaseError ("Error while executing SQL query: SELECT * FROM component WHERE (kind #> Array['spec', 'build', 'buildpack', 'port']) @> ? . ERROR: operator does not exist: jsonb @> bigint Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 85.",errorCode=0,sqlState="42883") When retrieving from dbeaver, it works.

Steps to Reproduce

Run above mentioned code.

Example jsonb column data entry: {"kind": "Component", "spec": {"type": "byocService", "build": {"buildpack": {"port": 8080, "version": "1.x", "language": "java", "outputDir": "build/", "nodeVersion": "16.3.0", "buildCommand": "npm run build", "dockerfilePath": "multi-protocol-service/Dockerfile", "dockerContextPath": "multi-protocol-service"}}, "source": {"github": {"path": "go/greeter", "repository": "https://github.com/Mirage20/byoc-service"}, "bitbucket": {"path": "go/greeter", "repository": "https://bitbucket.example.com/Mirage20/byoc-service"}}}, "status": {"phase": "Pending", "createdAt": "2021-06-01T12:00:00Z", "updatedAt": "2021-06-01T12:00:00Z", "componentId": "12345678-1234-1234-1234-123456789012"}, "metadata": {"name": "component1", "description": "Component 1", "displayName": "Component 1", "projectName": "project1"}, "apiVersion": "core.choreo.dev/v1alpha1"}

Affected Version(s)

2201.8.4

OS, DB, other environment details and versions

No response

Related area

-> Compilation

Related issue(s) (optional)

No response

Suggested label(s) (optional)

No response

Suggested assignee(s) (optional)

No response

[daneshk]

@chalindukodikara Apologies for the delay in providing the correct approach. The issue was with the type of the port value we passed in the select query. It needs to be of type JSONBinaryValue instead of int. Please update the Ballerina code accordingly with the following correction

public function main() returns error? {
    postgresql:Client dbClient = check new(
        host = "localhost",
        port = 5432,
        username = "postgres",
        password = "postgres",
        database = "test"
    );
    postgresql:JsonBinaryValue port = new(8080);

    sql:ParameterizedQuery kindSearchQuery =
            `SELECT * FROM component WHERE (kind #> Array['spec', 'build', 'buildpack', 'port']) @> ${port}`;
    stream<ComponentKindResultStream, sql:Error?> componentKindResultStream = dbClient->query(kindSearchQuery);
    ComponentKindResultStream[]|error componentKinds = from var x in componentKindResultStream
        let var idValue = x.id, var kindValue = x.kind
        group by idValue, kindValue
        select {
            id: idValue,
            kind: kindValue
        };
    io:println(componentKinds);
}

type ComponentKindResultStream record {
    string id;
    string kind;
};

[daneshk]

Solution provided. Hence closing the issue

[github-actions[bot]]

This issue is NOT closed with a proper Reason/ label. Make sure to add proper reason label before closing. Please add or leave a comment with the proper reason label now.

      - Reason/EngineeringMistake - The issue occurred due to a mistake made in the past.
      - Reason/Regression - The issue has introduced a regression.
      - Reason/MultipleComponentInteraction - Issue occured due to interactions in multiple components.
      - Reason/Complex - Issue occurred due to complex scenario.
      - Reason/Invalid - Issue is invalid.
      - Reason/Other - None of the above cases.