v18 CRM SQL Unintended Consequences

[skywaveorg]

This is the WHERE clause in my SQL statement:

WHERE JSON_EXTRACT(person, JSON_UNQUOTE(JSON_SEARCH(person, "one", '%123%', NULL, "$.phoneNumbers[*].canonicalForm"))) IS NOT NULL;

3CX looks for anything wrapped in brackets and replaces it. For JSON_SEARCH, I'm doing a wildcard search and it detects the [*] as a replacement and completely removed it from the SQL statement.

I tried CONCAT to build the JSON search query but SQL just returns all entries as NULL.

WHERE JSON_EXTRACT(person, JSON_UNQUOTE(JSON_SEARCH(person, "one", '%123%', NULL, CONCAT("$.phoneNumbers[", "*].canonicalForm")))) IS NOT NULL;

How can this be resolved?

 

[Evolute IT]

Try using {{*}} instead of [*]

This is normally how we do in CRM templates.

 

[skywaveorg]

It is still stripping it completely, unfortunately.

 

[Evolute IT]

It is still stripping it completely, unfortunately.

Hmm, that should not happen. It should be either left as {{}} or replaced by [ ].

https://www.3cx.com/docs/server-side-crm-template-xml-description/#h.2oikrv8ncny6

 

[skywaveorg]

Hmm, that should not happen. It should be either left as {{}} or replaced by [ ].

https://www.3cx.com/docs/server-side-crm-template-xml-description/#h.2oikrv8ncny6

Maybe the SQL statement check is using something different to check for brackets?

 

[Evolute IT]

Well, it seems so.

Try usign a stored procedure instead? This way, the "actual" query with [ ] is in SQL and the 3CX just calls "LookupNumber('[Number]');" that's it.

 

[skywaveorg]

Well, it seems so.

Try usign a stored procedure instead? This way, the "actual" query with [ ] is in SQL and the 3CX just calls "LookupNumber('[Number]');" that's it.

I am already doing that, but I'd like to just have the statement stored in 3CX haha

 

[edossantos]

The expression evaluator is the same for SQL or REST requests. Can you show us how you're configuring your SQL statement now? And what you see when you run a test lookup from the 3CX Console > Settings > CRM > Test button?

 

[skywaveorg]

@edossantos

SQL Statement

SELECT id AS contactId,
JSON_UNQUOTE(JSON_EXTRACT(person, "$.names[0].givenName")) AS firstname,
JSON_UNQUOTE(JSON_EXTRACT(person, "$.names[0].familyName")) AS lastname,
JSON_UNQUOTE(JSON_EXTRACT(person, "$.organizations[0].name")) AS companyname,
JSON_UNQUOTE(JSON_EXTRACT(person, "$.emailAddresses[0].value")) AS email,
JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "mobile", NULL, "$.phoneNumbers[*].type"), ".type", ".canonicalForm")))) AS phonemobile,
JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "home", NULL, "$.phoneNumbers[*].type"), ".type", ".canonicalForm")))) AS phonehome,
JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "work", NULL, "$.phoneNumbers[*].type"), ".type", ".canonicalForm")))) AS phonebusiness,
JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "other", NULL, "$.phoneNumbers[*].type"), ".type", ".canonicalForm")))) AS phoneother,
JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "workFax", NULL, "$.phoneNumbers[*].type"), ".type", ".canonicalForm")))) AS faxbusiness,
JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "homeFax", NULL, "$.phoneNumbers[*].type"), ".type", ".canonicalForm")))) AS faxhome
FROM contacts
WHERE JSON_EXTRACT(person, JSON_UNQUOTE(JSON_SEARCH(person, "one", "%[Number]%", NULL, "$.phoneNumbers[*].canonicalForm"))) IS NOT NULL;

Test CRM integration with phone number 555-1234

Started scenario with empty id
Request text: ConnectionString: Server=10.20.1.50;Port=3306;Database=contacts;User Id=contacts_pbx;Password=1CWnPEQbXakn7S9D
Statement: SELECT id AS contactId, JSON_UNQUOTE(JSON_EXTRACT(person, "$.names.givenName")) AS firstname, JSON_UNQUOTE(JSON_EXTRACT(person, "$.names.familyName")) AS lastname, JSON_UNQUOTE(JSON_EXTRACT(person, "$.organizations.name")) AS companyname, JSON_UNQUOTE(JSON_EXTRACT(person, "$.emailAddresses.value")) AS email, JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "mobile", NULL, "$.phoneNumbers.type"), ".type", ".canonicalForm")))) AS phonemobile, JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "home", NULL, "$.phoneNumbers.type"), ".type", ".canonicalForm")))) AS phonehome, JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "work", NULL, "$.phoneNumbers.type"), ".type", ".canonicalForm")))) AS phonebusiness, JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "other", NULL, "$.phoneNumbers.type"), ".type", ".canonicalForm")))) AS phoneother, JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "workFax", NULL, "$.phoneNumbers.type"), ".type", ".canonicalForm")))) AS faxbusiness, JSON_UNQUOTE(JSON_EXTRACT(person, JSON_UNQUOTE(REPLACE(JSON_SEARCH(person, "one", "homeFax", NULL, "$.phoneNumbers.type"), ".type", ".canonicalForm")))) AS faxhome FROM contacts WHERE JSON_EXTRACT(person, JSON_UNQUOTE(JSON_SEARCH(person, "one", "%551234%", NULL, "$.phoneNumbers.canonicalForm"))) IS NOT NULL;

Response text: []
Response finished
Finished scenario with empty id
Started CreateContactRecord
Response finished
Finished CreateContactRecord
Result obtained

What I immediately noticed is that the first digit of the 555-1234 number is cut off, which I'm not sure why. That might be the issue?

 

[Evolute IT]

Here's a quick easy fix: build a small PHP script doing the lookup and returning the data. Then create a custom CRM integration template using the tool.

Instead of going Database directly and messing around for days.

A script can do that in 10 lines.

 

[skywaveorg]

Here's a quick easy fix: build a small PHP script doing the lookup and returning the data. Then create a custom CRM integration template using the tool.

Instead of going Database directly and messing around for days.

A script can do that in 10 lines.

I coded an app that syncs contacts across all users in Google, and I already have the contacts stored in my DB and since the app isn't run in a web server, just querying the DB would be easiest. Plus, 3CX supports SQL statements for the CRM so I would want to be able to use that feature since it's supported.

 

[Evolute IT]

I coded an app that syncs contacts across all users in Google, and I already have the contacts stored in my DB and since the app isn't run in a web server, just querying the DB would be easiest. Plus, 3CX supports SQL statements for the CRM so I would want to be able to use that feature since it's supported.

Yes they do support SQL, but your statement is using JSON functions and you're having issues that are probably due to how the system is literally coded, so I don't think you'll ever be able to get it to work with the JSON.

Instead, why not save the data as columns in a table instead of a full JSON? (For JSON, I'd go MongoDB just because it is made for this.)

 

[skywaveorg]

Yes they do support SQL, but your statement is using JSON functions and you're having issues that are probably due to how the system is literally coded, so I don't think you'll ever be able to get it to work with the JSON.

Instead, why not save the data as columns in a table instead of a full JSON? (For JSON, I'd go MongoDB just because it is made for this.)

I don't control the JSON structure of Google Contacts. If they ever change anything I'd have to update the entire table scheme. If I grab the contact JSON structure, I can then have my app grab whatever needed from that structure without fail. Also SQL supports JSON columns and allows for querying and indexing. It works great and is supported

I think the issue is just some type of parsing issue. The first digit is cut off for some reason in my SQL statement when querying for the number and if that is resolved then everything will work.

 

[Evolute IT]

Well, I can't help any further as I don't have the 3CX source code to figure out the parsing issue.

All I can say is: your argument is invalid. Changing JSON structure doesn't mean changing the table or the database, just your script compiling the data.

Anyway, I gave suggestions. @edossantos may know more.

 

[edossantos]

If the initial 5 is being cutted off, that's due to the configuration you have in the 3CX Console > Advanced > Contacts > Options. You need to specify Match Exactly there in order to use the full number. That's something you can try and see if it makes any difference.

I also see a casing problem in your select statement, which starts with:
SELECT id AS contactId,

But it should be:
SELECT id AS contactid,

In any case, this is not causing the issue, because from your logs the database is not returning any data. This could be because of the [0] being removed from the query. This happens because anything in square brackets is considered a variable. Unknown variables are returned as empty strings, and that's why the [0] is being removed. You should try with:
JSON_UNQUOTE(JSON_EXTRACT(person, "$.names{{0}}.givenName")) AS firstname,

If this doesn't work, then probably a stored procedure as Frederick as suggested is something else to try...

Finally, also as Frederick has mentioned, storing the full JSON in an SQL database doesn't seem to be a good idea. You could use MongoDB which is also supported by 3CX, and create a template that matches the JSON structure you have.

 

[skywaveorg]

If the initial 5 is being cutted off, that's due to the configuration you have in the 3CX Console > Advanced > Contacts > Options. You need to specify Match Exactly there in order to use the full number. That's something you can try and see if it makes any difference.

I also see a casing problem in your select statement, which starts with:
SELECT id AS contactId,

But it should be:
SELECT id AS contactid,

In any case, this is not causing the issue, because from your logs the database is not returning any data. This could be because of the [0] being removed from the query. This happens because anything in square brackets is considered a variable. Unknown variables are returned as empty strings, and that's why the [0] is being removed. You should try with:
JSON_UNQUOTE(JSON_EXTRACT(person, "$.names{{0}}.givenName")) AS firstname,

If this doesn't work, then probably a stored procedure as Frederick as suggested is something else to try...

Finally, also as Frederick has mentioned, storing the full JSON in an SQL database doesn't seem to be a good idea. You could use MongoDB which is also supported by 3CX, and create a template that matches the JSON structure you have.

The contact is stored as a JSON in a column since the google client library requires the complete JSON to extrapolate it into the relevant objects. If I were to take the time and split up the entire contact into a table, I would have to code something to rebuild it into a usable contact to send back to Google, and waste processing doing this constantly. Since I only care about which contact is which, that is identified in its own column so I can identify a contact by a single ID value rather than having to search and compare names, phone numbers, emails, etc. The only analytical processing of the JSON is done with the query I made being used by 3CX.

I agree that storing JSON in a single column is not usually a great idea, and since 3CX is the only thing querying the JSON column for information, it doesn't make sense to do this in our case.

Thank you for the feedback!