How to query an array within an array with couchbase

Question

Hi I struggle to query this object

{
    "$fieldlog": [
       {
         "dt": "2024-03-12T08:05:55+01:00",
         "fields": [
           {
            "field": "firstname",
             "newvalue": "Ivonne3",
             "oldvalue": "Ivonne"
           }
         ],
        "user": "user1"
       },
       {
         "dt": "2024-03-12T08:06:29+01:00",
         "fields": [
           {
             "field": "firstname",
             "newvalue": "Ivonne",
             "oldvalue": "Ivonne3"
           }
         ],
         "user": "user2"
       }
     ]
 }

I tried the ANY element SATISFIES ANY field SATISFIES element.field.newvalue = "Ivonne" END END but got no luck. I am looking for an simple way to query this kind of Object

Answer 1

The simplest way to manage array with Couchbase would be to use unnest. So you can run the following query:

SELECT  * FROM `default` unnest `$fieldlog` f unnest f.fields fields Where fields.newvalue = "Ivonne3"