• Print
close
Breaking news

YouTube users’ ads secretly mine cryptocurrencies from user’s computer T...read more

JSON decode in MySQL Query

Payment gateway response or some other third party API return responses in JSON format and we need to save for future tracking or for logging.

In that case, we generally saved entire JSON response in a text column in the database. So that we can get that JSON and easily decode using json_decode() in PHP or any other language/application.

The problem arises when we need to get some data or key fields from that JSON string by using MySQL query for any reporting purpose. Generally, we cant get data from that JSON string by using simple MySQL query.

But, In this blog, I want to show you some MySQL tricks by using that you can get data from JSON string from MYSQL query. In short, you can say this trick is

JSON decode in MySQL Query.

Now dig into the trick.

Let’s take an example column of JSON string that contains a JSON value:

{ "name":"John", "age":31, "city":"New York" }

So in that JSON, let say we need to get the age field value. To get that we use following MySQL query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('{ "name":"John", "age":31, "city":"New York" }','age":',-1),',',1)

This query gives you the result: 31.

Now let’s explain this query. In this query, I use SUBSTRING_INDEX() that return string from string before number of occurrences of delimiter, a similar function of MID() of Excel or substr() of PHP.

Syntax of SUBSTRING_INDEX is

SUBSTRING_INDEX(string, delimiter, number)

Where:

‘string’ is the source string.

‘delimiter’ to search for in string.

‘number’ of times to search for delimiter.

In the above MySQL query, I use the JSON string in ‘string’ parameter, age”: in ‘delimiter’ as I want to take string from here. I use -1 in ‘number’. Here something to be noted,

If number is negative, the SUBSTRING_INDEX() function returns everything from the end of the string to the targeted delimiter
If number is positive, the SUBSTRING_INDEX() function returns everything from the start of the string to the targeted delimiter

As I need the text after the delimiter: age, here I use the negative number.

Now the inner SUBSTRING_INDEX() gives me this string:

SELECT SUBSTRING_INDEX('{ "name":"John", "age":31, "city":"New York" }','age":',-1)

Result: 31, “city”:”New York” }

But I need only 31 that is before the , (comma). So I again use SUBSTRING_INDEX() on the given result and this time I use comma as delimiter and a positive number as I need the text of before delimiter.

So as per syntax, my upper SUBSTRING_INDEX() will be:

SUBSTRING_INDEX(string,',', 1)

and in string part will be the inner SUBSTRING_INDEX() part. That’s complete the entire query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('{ "name":"John", "age":31, "city":"New York" }','age":',-1),',',1)

Hope, you enjoy that trick and you will use this trick in your work.

For any query/ suggestion or anything else comment below. I will respond as soon as possible.

Tags: , ,

No Comments

Leave a reply

Post your comment
Enter your name
Your e-mail address

Story Page