0

My sample Data:

%7B%22message%22%3A%22hi%22%2C%22message_type%22%3A%22text%22%2C%22nickName%22%3A%22Srinivasanvasan%20Prakasam%22%2C%22utcTimestamp%22%3A1652999988927%7D

i used JSON_EXTRACT function but i am getting

Invalid JSON error

Shadow
  • 33,525
  • 10
  • 51
  • 64
kumaran
  • 366
  • 1
  • 8
  • 1
    That's not JSON, why do you think you can use `JSON_EXTRACT()`? – Barmar May 24 '22 at 06:30
  • 1
    Apparently that's url-encoded JSON. MySQL doesn't have a URL decode function, so you'll need to extract what you want in another programming language. – Barmar May 24 '22 at 06:32
  • 1
    Or you could fix the way you store the data by not using URL encoding. – Barmar May 24 '22 at 06:32
  • You'll need to URL-decode it first, which can be done using this ugly function: https://stackoverflow.com/questions/6993754/mysql-select-with-url-decode. Or you could do what @Barmar suggested and use another programming language to do the unescaping. – Julia May 24 '22 at 06:33
  • @Julia, Yes i tried those functions that you mentioned. But, if its is large data it takes some time to process. I need only messge_type value – kumaran May 24 '22 at 06:36

1 Answers1

0

I think you first need to decode the url and then upload to database in order to do JSON_EXTRACT.

Anirudh Vasudev
  • 3
  • 3
  • Thanks for your reply. I tried those functions that you mentioned. But, if its is large data it takes some time to process. So that only I asked particular value – kumaran May 24 '22 at 06:39
  • Best way to speed up the process of fetching data is by creating different columns or tables as per the requirement. Hope it helps. – Anirudh Vasudev May 25 '22 at 08:17