1

Let's say I have a JSON object with the following structure:

{
   "_id":"0000abcdefg",
   "type":"PP",
   "subscription":{
      "subscribers":{
         "physicSubscribers":[
            {
               "civility":"M",
               "lastname":"DOE",
               "firstname":"John",
               "emailAddress":"john-doe@something.com",
            },
            {
               "civility":"M",
               "lastname":"smith",
               "firstname":"TED",
               "emailAddress":"ted-smith@something.com",
            }
         ]
      }
   }
}

How can I search for the documents by subscription.subscribers.physicSubscribers[*].firsname, but converting the value to lowercase before comparing?

I have tried some solutions this, but it is always returning an empty result:

SELECT doc ->> '$' 
    FROM customers 
    WHERE lower(JSON_EXTRACT(doc,'$.subscription.subscribers.physicSubscribers[*].firstname')) = 'john'

Thank you!

Jeff Mira
  • 25
  • 5

1 Answers1

2

Using JSON_EXTRACT() in WHERE looks incorrect - it returns an array which you try to compare with single value. So you must search within this array additionally.

Yuou may use JSON_SEARCH, and specify correct collation:

SELECT doc ->> '$'
FROM customers
WHERE JSON_SEARCH(doc, 
                  'one', 
                  'john' COLLATE utf8mb4_0900_ai_ci, 
                  NULL, 
                  '$.subscription.subscribers.physicSubscribers[*].firstname') IS NOT NULL

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=12d9f1c860d5433e26bbf9279c92f09c

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Thank you @Akina, I have tried just adding a LIKE in the query (lower(JSON_EXTRACT(doc,'$.subscription.subscribers.physicSubscribers[*].firstname')) like '%john%' ) and it is working now even with my initial solution, however yours seems that works better. QQ - what is the meaning of this 'COLLATE utf8mb4_0900_ai_ci'? – Jeff Mira May 26 '21 at 14:07
  • @JeffMira [Character Sets, Collations, Unicode](https://dev.mysql.com/doc/refman/8.0/en/charset.html). of course, this is a huge array of information, but I think it must be read completely at least once. – Akina May 26 '21 at 16:43