Search code examples
mysqljsondatabasemysql-workbenchmysql-json

How to escape a period in mySql JSON query

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: [email protected] or [email protected].

And due to presence of period in keys i am unable to execute json queries in DB. Like: SELECT JSON_EXTRACT(analytics, "[email protected]") FROM EmailsData; Does not work due to presence of periods in [email protected] 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?

Solution

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

    '$."[email protected]"'

    Please give it try.