CODEWITHSUNDEEP
sqlmysqlselectsearchmysql-json
user1034461
user1034461

Reputation:

Filter by a value in JSON for any key

Is there a way in MySQL to use JSON_SEARCH() or INSTR() to filter rows by the whole word inside of any available JSON values without knowing their keys?

Test data - but keys can be other locales, not only those provided here:

{ "hr_HR":"Konzum Zagreb Tower", "sr_RS":"Konzum in Zagreb Tower", "en_US":"Konzum Zagreb Tower" }
{ "hr_HR":"Wolt Zagreba",        "sr_RS":"Wolt Zagrebu",           "en_US":"Wolt Zagreb"         }
{ "hr_HR":"Glovo Zagreb",        "de_DE":"Glovo Zagreb",           "en_US":"Glovo Zagreb"        }
{ "hr_HR":"Bolt Zagreb",         "it_IT":"Bolt Zagreb",            "en_US":"Bolt Zagreb"         }
{ "hr_HR":"Mlinar - Zagreb",     "sr_RS":"Mlinar - Zagreb",        "fr_FR":"Zagreb Mlinar"       }
{ "hr_HR":"Curry Bowl Zagreb",   "sr_RS":"Curry Bowl Zagreb",      "en_GB":"Curry Bowl Zagreb"   }
{ "hr_HR":"INA - Siget",         "sr_RS":"INA - Siget",            "en_IE":"INA - Siget"         }
{ "hr_HR":"INA",                 "sr_RS":"INA",                    "en_US":"INA"                 }
{ "hr_HR":"INA - Selska",        "sr_RS":"INA - Selska",           "en_US":"INA - Selska"        }

This one does not work as expected (meaning my expectation was that * would be marking any key):

SELECT id, name FROM provider
WHERE
    INSTR(
        CONCAT( ' ', LOWER( name->>'$.*' ), ' ' ),
        CONCAT( ' ', 'zagreb', ' ' )
    ) > 0;

The query that works for a given key/locale (hardcoded):

SELECT id, name FROM provider
WHERE
    INSTR(
        CONCAT( ' ', LOWER( name->>'$.hr_HR' ), ' ' ),
        CONCAT( ' ', 'zagreb', ' ' )
    ) > 0;

But I don't want to hardcode all keys/locales into a query and use OR to check them all:

SELECT id, name
FROM provider
WHERE 
       INSTR(CONCAT(' ', LOWER(NAME->>'$.hr_HR'), ' '), CONCAT(' ', 'zagreb', ' ')) > 0
    OR INSTR(CONCAT(' ', LOWER(NAME->>'$.sr_RS'), ' '), CONCAT(' ', 'zagreb', ' ')) > 0
    OR INSTR(CONCAT(' ', LOWER(NAME->>'$.en_US'), ' '), CONCAT(' ', 'zagreb', ' ')) > 0
    OR INSTR(CONCAT(' ', LOWER(NAME->>'$.en_GB'), ' '), CONCAT(' ', 'zagreb', ' ')) > 0
    OR INSTR(CONCAT(' ', LOWER(NAME->>'$.en_IE'), ' '), CONCAT(' ', 'zagreb', ' ')) > 0
    OR INSTR(CONCAT(' ', LOWER(NAME->>'$.fr_FR'), ' '), CONCAT(' ', 'zagreb', ' ')) > 0
    OR INSTR(CONCAT(' ', LOWER(NAME->>'$.it_IT'), ' '), CONCAT(' ', 'zagreb', ' ')) > 0;

Upvotes: 0

Views: 46

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

In MySQL 8.0 you can use JSON_TABLE() twice, to get keys and then to get values:

SELECT id, l.locale, j.location
FROM provider
CROSS JOIN JSON_TABLE(JSON_KEYS(name), '$[*]'
    COLUMNS (locale VARCHAR(10) PATH '$')) AS l
CROSS JOIN JSON_TABLE(JSON_EXTRACT(name, CONCAT('$.', l.locale)), '$'
    COLUMNS (`location` varchar(20) PATH '$')) AS j
WHERE j.location LIKE '%Zagreb%' ;

Output:

+----+--------+---------------------+
| id | locale | location            |
+----+--------+---------------------+
|  1 | en_US  | Konzum Zagreb Tower |
|  1 | hr_HR  | Konzum Zagreb Tower |
|  2 | en_US  | Wolt Zagreb         |
|  2 | hr_HR  | Wolt Zagreba        |
|  2 | sr_RS  | Wolt Zagrebu        |
|  3 | de_DE  | Glovo Zagreb        |
|  3 | en_US  | Glovo Zagreb        |
|  3 | hr_HR  | Glovo Zagreb        |
|  4 | en_US  | Bolt Zagreb         |
|  4 | hr_HR  | Bolt Zagreb         |
|  4 | it_IT  | Bolt Zagreb         |
|  5 | fr_FR  | Zagreb Mlinar       |
|  5 | hr_HR  | Mlinar - Zagreb     |
|  5 | sr_RS  | Mlinar - Zagreb     |
|  6 | en_GB  | Curry Bowl Zagreb   |
|  6 | hr_HR  | Curry Bowl Zagreb   |
|  6 | sr_RS  | Curry Bowl Zagreb   |
+----+--------+---------------------+

If you can change the format of the JSON documents:

INSERT INTO provider (name) VALUES
('[{ "locale": "hr_HR", "location": "Konzum Zagreb Tower"}, {"locale": "sr_RS", "location":"Konzum in Zagreb Tower"}, {"locale": "en_US", "location":"Konzum Zagreb Tower" }]'),
('[{ "locale": "hr_HR", "location": "Wolt Zagreba"},        {"locale": "sr_RS", "location":"Wolt Zagrebu"},           {"locale": "en_US", "location":"Wolt Zagreb"         }]'),
('[{ "locale": "hr_HR", "location": "Glovo Zagreb"},        {"locale": "de_DE", "location":"Glovo Zagreb"},           {"locale": "en_US", "location":"Glovo Zagreb"        }]'),
('[{ "locale": "hr_HR", "location": "Bolt Zagreb"},         {"locale": "it_IT", "location":"Bolt Zagreb"},            {"locale": "en_US", "location":"Bolt Zagreb"         }]'),
('[{ "locale": "hr_HR", "location": "Mlinar - Zagreb"},     {"locale": "sr_RS", "location":"Mlinar - Zagreb"},        {"locale": "fr_FR", "location":"Zagreb Mlinar"       }]'),
('[{ "locale": "hr_HR", "location": "Curry Bowl Zagreb"},   {"locale": "sr_RS", "location":"Curry Bowl Zagreb"},      {"locale": "en_GB", "location":"Curry Bowl Zagreb"   }]'),
('[{ "locale": "hr_HR", "location": "INA - Siget"},         {"locale": "sr_RS", "location":"INA - Siget"},            {"locale": "en_IE", "location":"INA - Siget"         }]'),
('[{ "locale": "hr_HR", "location": "INA"},                 {"locale": "sr_RS", "location":"INA"},                    {"locale": "en_US", "location":"INA"                 }]'),
('[{ "locale": "hr_HR", "location": "INA - Selska"},        {"locale": "sr_RS", "location":"INA - Selska"},           {"locale": "en_US", "location":"INA - Selska"        }]');

Then it could be done with a single use of JSON_TABLE().

SELECT id, j.locale, j.location
FROM provider
CROSS JOIN JSON_TABLE(name, '$[*]'
    COLUMNS (`locale` VARCHAR(10) PATH '$.locale', `location` varchar(20) PATH '$.location')) AS j
WHERE j.location LIKE '%Zagreb%' ;

Or if you forget about storing data in JSON, it will be even easier:

SELECT id, location
FROM provider
WHERE locale LIKE '%Zagreb%';

This brings up the point that using JSON is harder than using normal rows and columns in a relational database. Trying to use JSON as though it is a relational table is an exercise in the Inner-Platform Effect.

Upvotes: 1

Related Questions