DoctrineJsonFunctions

DoctrineJsonFunctions copied to clipboard

How to reproduce

in the database field I store this JSON document

{
  "site": "2dbc9c35-bf39-433c-b596-6b08aa941bac",
  "bitrix24": 123456,
  "mobile_backend": "5624"
}

In read model I try to fetch documents with bitrix24 = 123456

$qb
->andwhere("JSON_EXTRACT(EXTERNAL_IDS, '$.bitrix24') = :external_system_id")
->setParameter(':external_system_id', 123456);
        
print_r($qb->getSQL());
print_r($qb->getParameters());

dump:

SELECT
 contacts.UUID as contact_id, 
 contacts.NAME as contact_name,
 contacts.EXTERNAL_IDS as contact_external_ids 
FROM 
test_table
WHERE
 (contacts.APP_CLIENT_ID =: APP_CLIENT_ID)
 AND
 (JSON_EXTRACT(EXTERNAL_IDS, '$.bitrix24') =: external_system_id)

Array
(
[: APP_CLIENT_ID] => 106
[: external_system_id] => 123456
)

But the select result is empty, if i hardcode parameters in SQL - it works, is I use sprintf to build sql-query they work too.

If i use parameters for both arguments

$qb->andwhere("JSON_EXTRACT(EXTERNAL_IDS, '$.:external_system_code') = :external_system_id")
 ->setParameter(':external_system_code', 'bitrix24')
 ->setParameter(':external_system_id', 123456);

i got an error in SQL query

An exception occurred while executing 
'
SELECT
 count(*) as cnt 
FROM 
(SELECT 
contacts.UUID as contact_id, 
contacts.NAME as contact_name,
contacts.EXTERNAL_IDS as contact_external_ids 
FROM test_table
WHERE
 (contacts.APP_CLIENT_ID = ?) AND (JSON_EXTRACT(EXTERNAL_IDS, '$.:external_system_code') = ?)) dbal_count_tbl' 

with params [106, 123456]:\n\nSQLSTATE[42000]: Syntax error or access violation: 3143 Invalid JSON path expression. The error is around character position 23.