0

I am working on a project where i am required to store data in JSON format using mySQL DB. I want to store data in this format:

{
 email1:{ data:"This is some data of email1", status:"registered", count:100 },
 email1:{ data:"This is some data of email2", status:"unregistered", count:230 },
}

Here email1 & email2 are two emails of someone acting as a key. while executing queries to store and retrieve data in this format, i discovered that emails could contain periods in them too, for example: max.jhon@gmail.com or master.blaster01@gmail.com.

And due to presence of period in keys i am unable to execute json queries in DB. Like: SELECT JSON_EXTRACT(analytics, "$.max.jhon@gmail.com") FROM EmailsData; Does not work due to presence of periods in $.max.jhon@gmail.com and give me this error :

Error Code: 3143. Invalid JSON path expression. The error is around character position 8.

Can someone suggest me how can i escape these periods inside query and if not then what will be best way to store data in JSON format for respective emails?

Kartik Gautam
  • 25
  • 1
  • 5

1 Answers1

2

You will need to use single quotes similar to the following:

'$."max.jhon@gmail.com"'

Please give it try.

IceCode
  • 1,466
  • 13
  • 22