Select json data from mysqli and sum it for prometheush-CodePudding

I have taken over the work on a certain service after a certain person. My current task is to capture data from MySQLi and throw it into metrics for prometheus. I was able to do this from one table, unfortunately the other table has data in json which is in the 'data' column. Example:

{
    'lang': "en",
    'space': {
        "hasOffice": "0",
        "surfaceType": 1,
        "officeAddress": "",
        "officeLocation": null
    },
    "terms": {
        "space": "economical",
        "office": 7,
        "rentTime": "3",
        "openspace": 40
    }
}

I have about 200 of these rows - and the number will increase. Is there any easy way to retrieve this data actively? For example, I would need to sum up how many rows of data have lang: en.

CodePudding user response：

If I understand you correctly, you have JSON data in DB? And you wont to query/search this data. And you have MySQL 5.7 (which supports JSONS)..

So what you could do is:

SELECT COUNT(*) FROM some_table WHERE JSON_EXTRACT(<json_column>,'$.lang') = 'en'

And this would return you back number of rows with en language set.

There are multiple ways of working with JSONs, please read more here: https://dev.mysql.com/doc/refman/5.7/en/json.html.

For example, you could do something like this in JSON:

SELECT * FROM some_table WHERE JSON_EXTRACT(<json_column>,'$.space.surfaceType') <> 1

And this would return back ALL records where surfaceType is not 1...There are several other options with JSONs.