How to search a value in a nested array inside a JSON in Mysql 8?

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":"[email protected]",
            },
            {
               "civility":"M",
               "lastname":"smith",
               "firstname":"TED",
               "emailAddress":"[email protected]",
            }
         ]
      }
   }
}

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!

Solution

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