Doctrine cant return results on json queries

Question

there may be threads with close titles but i couldn't manage to solve this issue. Following query returns expected results when called from psql command line or any Postgresql client application but when i implement the query with Doctrine 2, i get the following error/exception.

raw_data field is in JSON type.

Query:

    SELECT
      DISTINCT (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER
    FROM schema.projects p
    WHERE p.raw_data :: JSONB ? 'company'
          AND (p.raw_data ->> 'company') :: JSONB ? 'userId'
          AND (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER > 0
          AND p.is_deleted = FALSE
      LIMIT 20;
-- Returns 20 results

Doctrine Implementation:

public function fetchResulst()
{
    $sql = "
      SELECT
        DISTINCT (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER
        FROM schema.projects p
        WHERE p.raw_data :: JSONB ? 'company'
            AND (p.raw_data ->> 'company') :: JSONB ? 'userId'
            AND (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER > 0
            AND p.is_deleted = FALSE
          LIMIT 20
      )";

    return $this->_em->getConnection()->executeQuery($sql)->fetchAll();
}

Response (Exception)

Doctrine \ DBAL \ Exception \ SyntaxErrorException
    An exception occurred while executing ' SELECT DISTINCT (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER FROM listing.projects p WHERE p.raw_data :: JSONB ? 'company' AND (p.raw_data ->> 'company') :: JSONB ? 'userId' AND (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER > 0 AND p.is_deleted = FALSE LIMIT 20 ) ': SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1" LINE 5: WHERE p.raw_data :: JSONB $1 'company' ^

The content of 'company' field on raw_data payload is as below.

"company": {
  "CompanyID": 112233445566,
  "URL": null,
  "CompanyName": "Some Real Estate Bla bla Contact Office",
  "PartyNature": "Contact Office",
  "CompanyType": 26,
  "SubNature": null,
  "Description": "",
  "DescriptionLocal": "",
  "ImagePath": null,
  "Phone1": "+90 987 111 11 11",
  "Phone2": "",
  "Fax": null,
  "ContactEmail": "somauser@example.com",
  "NatureID": null,
  "PartySubNatureID": null,
  "CityID": "123",
  "CountyID": "456",
  "DistrictID": "789",
  "id": 14487,
  "userId": 35754
}

Versions: Postgresql: PostgreSQL 9.5.2 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.2 (clang-700.1.81), 64-bit

Doctrine - DBAL : v2.5.2

score 1 · Accepted Answer · edited May 23 '17 at 11:58

I guess, it is not aobut your data or fields, it is related with PDO and their bind parameter approach. You can easily handle error if you dont use ? condition. Because doctrine dbal and of course pdo (in the base) suppose this ? condition opeator as a query binding parameter and it is thr to convert this bind parameter to match a variable value. Bu in fact you have not any value to match. Because it is only conditional opeartor.

To handle this issue you can easily use jsonb_exists(column_name, key) function of postgres. I try to create a correct version of your example with jsonb_exists function below.

    $sql = "
  SELECT
    DISTINCT (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER
    FROM schema.projects p
    WHERE jsonb_exists(p.raw_data :: JSONB, 'company')
        AND jsonb_exists((p.raw_data ->> 'company') :: JSONB, 'userId')
        AND (((p.raw_data ->> 'company') :: JSONB) ->> 'userId') :: INTEGER > 0
        AND p.is_deleted = FALSE
      LIMIT 20
  ";
    $em  = $this->getServiceLocator()->get('doctrine.entitymanager.orm_default');

    var_dump($em->getConnection()->executeQuery($sql)->fetchAll()); exit;

I tested. Both of the query give same result.

Lots of db libraries have this issues:

Doctrine cant return results on json queries

1 Answers1