MySQL Error Code: 1305. FUNCTION JSON_EXTRACT does not exist in MySQL client version: 5.5.52

Question

Goal :- json_extract does not exist error.

I do have message body like this.

  < message type = "chat"
  to = "[email protected]"
  from = "[email protected]/9fs4kgn090" > < body > {
    "opponent_xmpp_id": "[email protected]",
    "latest_message_id": "6233"
  } < /body><active>http:/ / jabber.org / protocol / chatstates < /active></message >

I am trying to extract opponent_xmpp_id and latest_message_id.

for that i have written a query like below.

SELECT  LEFT(ExtractValue( stanza, "//@from" ),LOCATE("@",ExtractValue( stanza, "//@from" )) - 1),
        LEFT(ExtractValue( stanza, "//@to" ),LOCATE("@",ExtractValue( stanza, "//@to" )) - 1),
        ExtractValue(stanza, "//body"),
        ExtractValue(stanza, "//@chattype"),
        TRIM(BOTH '"' FROM json_extract(ExtractValue(stanza, "//body"), '$.opponent_xmpp_id')) AS opponent_xmpp_id,
        json_extract(ExtractValue(stanza,"//body"),'$.latest_message_id') AS latest_message_id        
FROM
ofOffline

causes error

1305 - FUNCTION databaseName.json_extract does not exist

As per i searched its supporting in > MYsql 5.7 version only.

So is there any function which do the simmilar job as json_extract in MySQL client version: 5.5.52 ?

Answer 1

The version of mysql client is not relevant. The functionality exists (or in your case does not exist) in the mysql server. Thus the only thing that's relevant is the mysql server version. And this functionality is available only in mysql server 5.7 onwards. Upgrading your client will not solve the problem, you need to upgrade your server.

Is there a work around? yes. PHP's json_decode

This can work because your query does not have a WHERE clause. You are looking at the entire table. So you can just as easily fetch all that data and json_decode and then do the processing in your PHP code. This is going to be very slow if you have a lot of data though.