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 ?

Thanks for your prompt respons! but unfortunatly as you have suggested i had update server version to 5.7.16 & i need to sort it out this stuff with mysql only with using trigger so eventually it works with json_extract function with updated mysql Server version: 5.7.16. — Shashank Shah
– Shashank Shah, Commented Nov 9, 2016 at 4:41

e4c5 · Accepted Answer · 2016-11-04 05:23:21Z

12

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.

answered Nov 4, 2016 at 5:23

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1305 - FUNCTION databaseName.json_extract does not exist

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1305 - FUNCTION databaseName.json_extract does not exist

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related