0

I have following JSON format in the mysql table statistics and the column is stats

{
     "stats":{
          "gender":{
             "male":40, //40 is percentage
             "female":50
          },
      "cities":[
         {
            "name":"Melbourne",
            "country":"AU",
            "percentage":20
         },
         {
            "name":"London",
            "country":"GB",
            "percentage":10
         },
         {
            "name":"Sydney",
            "country":"AU",
            "percentage":14
         }
      ]
   }
}

What I know (Using -> or JSON_EXTRACT):

select * from statistics as a where a.stats->'$.stats.gender.male' < 41

It returns the above row since male percentage is 40.

Requirement:

I need to fetch records of country AU and percentage 20.

Any suggestion will be grateful.

karthick
  • 5,998
  • 12
  • 52
  • 90

1 Answers1

1

One option is to use JSON_CONTAINS function.

Test:

SET @`country` := '"AU"',
    @`percentage` := '20';

SELECT
  `stats`,
  `a`.`stats` -> '$.stats.cities[*].country',
  `a`.`stats` -> '$.stats.cities[*].percentage'
FROM
  `statistics` `a`
WHERE
  JSON_CONTAINS(`a`.`stats` -> '$.stats.cities[*].country', @`country`) AND
  JSON_CONTAINS(`a`.`stats` -> '$.stats.cities[*].percentage', @`percentage`);

See db-fiddle.

wchiquito
  • 16,177
  • 2
  • 34
  • 45